merging specific worksheets from multiple workbooks into one new workbook

Hi,

Can someone provide a code for this?

Currently, there is a workbook for each location stored in one directory. Inside each workbook are different datasets separated by worksheets. The setup in the workbooks are the same. They all have about 7-10 different worksheets with the same name. For example, there's an "Overview" sheet, if there's credit card data, there will be a "Credit Card" sheet, and same goes with cash. I would like to create a macro to open all the workbooks and extract the credit card and cash sheets and compile it into a new workbook for further analysis.

Thanks in advance for the much needed help!

Attached are 3 samples of the spreadsheets' layout for each location and "Master" is the way I would like the results to be. I want to create a macro that opens the files from the directory (file name needs to be dynamic cause there's a date attached at the end) and then pick up the specific worksheets if there's data at that location and combine it all into the "Master". I hope that makes sense.

I found your code below and tried to edit parts of it (in red) but got stuck with runtime error '438' at

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Am I approaching this correctly? I haven't attempted to add the two extra columns I want as headers in the Master Sheet. Hopefully I didn't confuse you more.

Thanks for the cleanup tip!

You are correct about the spelling of the sheet name error. Now I am getting runtime error '1004': Method 'Range of object'_Worksheet' failed at the line in red below

Please Login or Register  to view this content.

Is it because the title starts at row 3 and the subsequent spreadsheets should copy just the data starting at row 4? Is that correct? The current code deleted the first 2 rows on the first sheet (named "Overall"), which isn't one of the sheets I needed. It also ran into error when I tried to insert the two new columns.

Each location has the same sheet names if there's data for it. The order may vary. Data on each sheet starts on row 3 (header). Row 1, cell 1 is the location; row 2 is empty. I will need to create two new columns in the Master workbook to differentiate: 'location' and 'method of payment'. I will probably put in the formula to input r1c1's value into the new location column, then a formula to input the tab's name in 'new method of payment' column before it gets consolidated into the Master.

Example:
Asia Workbook: 5 sheets: "Overall", "Cash", "Checks", "Wires", etc
Europe Workbook: 7 sheets: "Overall", "Cash", "Checks", "Wires", "Credit Cards", etc
America Workbook: 7 sheets: "Overall", "Cash", "Wires", "Checks", "Credit Cards", etc