I have an inherited (and sometimes still-updated) workbook with many worksheets (currently 50+).
Each of these tabs has a table in the format of:
segment_name | subsegment | subnet | ipaddress
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
ASD | ASD-FGH | 10.10.10 | 10.10.10.17-40
| ASD-JKL | 192.168.100 | 192.168.100.50-200
Cells A1 through D1 are always named the same thing on each tab, always in the same order
A2 is a merged cell the height of the whole table (not all are the same height)
A2 is also the same name as the tab/worksheet name (eg ASD, DFG, GHJ, etc)
If this was a one-off, I’d manually copy/paste the content of each tab’s table into a new worksheet, unmerge the merged cells, filldown the empty spaces, and move on with my life
However, since the source workbook does get updated/changed periodically (new IPs/IP ranges added, removed, etc), I need a way to rebuild the simplified format (which is ultimately being converted into a CSV lookup table for Splunk) from the source on-demand
I presume there’s a clever (or even not-so-clever) way to do this with an Excel macro/VBA. However, I’m not sure how to go about it.
The logic should look something like this:
create tempsheet
foreach worksheet as currentsheet
if currentsheet.A1="segment_name"
append all rows from currentsheet.B -> currentsheet.last-populated to the end of tempsheet
else
advance to next worksheet
How would this actually look in VBA?
Is there a better/simpler way to do this?
