Update multiple workbooks with data from one workbook

1) Can you provide an example workbook with the EXACT same kind of data to export, not oversimplified in any way? We are looking for a logical way we can identify the keyword that would be used in each output row to know which "group" it goes with.

2) Are all the external workbooks in a specific dedicated folder?

3) Are the sheets within each external workbook named for the exact same text strings we are truing to identify in step #1? (it's important they be an exact match to keep things simple)

So is it accurate to say the sheet names are an exact match to the text found before the " - " string (up to 30 characters)?


WHEAT-SRW - CHICAGO BOARD OF TRADE
CORN - CHICAGO BOARD OF TRADE
CALIFORNIA CARBON ALLOWANCE VINTAGE 2015 - ICE FUTURES ENERGY DIV
CALIF CARBON ALL VINTAGE 2016 - ICE FUTURES ENERGY DIV
PJM TRI-RECs CLASS 1 Vin 2017 - ICE FUTURES ENERGY DIV
GULF # 6 FUEL 3.0% SULFUR SWAP - NEW YORK MERCANTILE EXCHANGE
ALGONQUIN CITY-GATES FINANCIAL BASIS - ICE FUTURES ENERGY DIV

etc?

Since sheet names can only be 30 characters long and assuming no illegal characters will ever be there?

Yes they are a exact match. Can we reduce the amount of characters to 20 in the sheets if possible?

so I have the sheet names limited to 20 characters:
"CANADIAN DOLLAR - CH"

edit:
Sorry, I read your post wrong. The sheet names are actually as described above, the 20 first characters of the string.
I was thinking of renaming the sheets to something simpler but Im fine in keeping them as is, 20 first char.

That's even better, if its always the first 20 characters and we don't have to search for the " - " then it shouldn't be too hard.

The macro basically goes inside the data workbook for exporting. When run:

1) Uses a specific known working folder
2) Opens each file in that folder one at a time
3) For each sheet in the opened file, takes the sheet name and applies a filter to the output data for matching rows in column A
4) If any rows match, copy all of them together to the bottom of the external workbook/sheet
5) Repeat for each sheet, repeat for each workbook.

Caveat, this does NOT insure every row is exported, it only insures that existing sheets in the external workbooks will have their matching data rows exported. It is possible for there to be output rows left that did not match to any sheet during the export routine.

Please Login or Register  to view this content.

Sorting the data on the resulting output sheets is easy. What columns should be used and in what order of importance?

Column C - Date- Descending
only this?

As for removing duplicates, that too is easy if we know which columns to use for determining duplicates.
Column C only?

If so, just add these two lines of code:

Please Login or Register  to view this content.

Awesome! thanks, it works perfectly now.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Hmm, maybe something like:

Please Login or Register  to view this content.