0

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?

warren
  • 9,920
  • 23
  • 86
  • 147
  • While not a *full* solution, I found https://www.extendoffice.com/documents/excel/2972-excel-save-export-convert-multiple-all-sheets-to-csv-text.html which may lead to an interim (ie multi-step) solution – warren Jul 01 '21 at 21:49
  • perhaps using data connections? – gns100 Jul 01 '21 at 23:16
  • @gns100 - what is a "data connection"? – warren Jul 02 '21 at 13:02
  • click on the menu item `Data` menu, the far left item should be `get and transform data` (previously known as powerQuery). you can use that to connect all of your workbooks into one new workbook. – gns100 Jul 02 '21 at 16:31

0 Answers0