Need macro to merge data from external workbooks into one master

'WORKBOOKS TO SHEETS
Here's a macro for collecting data from all files in a specific folder. This version copies the sheet in as a whole.

Workbooks to Sheets - PART 2

The parts of the code that need to be edited are colored to draw your attention.

This macro disables other macro events near the top of the macro, this is the snippet that does that, FYI.

Please Login or Register  to view this content.

THis part will need to be edited to grab sheet(2) data instead of Sheet(1).

Please Login or Register  to view this content.

This is perfect - thank you so much! I appreciate your time and help.

I did run into a runtime error when executing the macro:

"Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and column than the source workbook. To move or copy the data to the destination workbook, you can select the data, and then use the Copy and Paste commands to insert it into the sheets of another workbook."

My source workbooks have ~4400 rows and go into column DR.

Here is my code:

Please Login or Register  to view this content.

You copying from .xlsm files into an .xls file? If you change your master workbook into an .xlsm file, too, you should be able to run it.


Along with tweaking those two lines of code, I noticed a third edit, a bunch of text removed from above the DIMs at the top. (pout)

I edited my last post, my apologies.

Thanks for the reply - it still has the same error though even after I saved the master workbook as an .xlsm before running the macro again... when I choose to debug it highlights the following line:

Please Login or Register  to view this content.

Don't know if it matters or not but the master workbook has Sheet1, Sheet2, Sheet3, and Capacity_Summary.


UPDATE: I closed out everything and reopened after saving the original master file as a .xlsm and it worked! Thanks again for your help!

Glad it's working for you, another successful use of that macro, one of the most frequently used from my site. Cheers!

Heh, I'd need to see the whole macro.

Your edits look solid. I can't fault the technique.

Further, since you have On Error Goto 0 active, then when the macro fails to copy the sheet over to the main workbook, it should give an error.


I guess you could give me a few test files and your master, I can do a step by step F8 walk through and see if I can spot where it doesn't do what you want.

1) This macro will probably fall over on data sets that full of merged cells. I don't do that.

2) You will most likely need to unprotect any sheet before you can copy from one workbook to another.

Originally Posted by JBeaucaire

1) This macro will probably fall over on data sets that full of merged cells. I don't do that.

2) You will most likely need to unprotect any sheet before you can copy from one workbook to another.

It turned out to be an issue with the protections because the BOW sheets work fine but the HC ones do not. They're both protected workbooks and both have merged cells.

The hurdle I'm testing through now is that when I manually copy the worksheet and then go to paste it in the master workbook, I get an alert saying there was already a named range in the worksheet that is the same as the one being copied into the worksheet - do you want to use the new one? I deleted the named range from the Name Manager in the master workbook and then am able to manually copy and paste the worksheet in but the macro still won't copy in the worksheet successfully. No errors thrown.

Any suggestions? Is there code for accepting alert defaults? It looks like the range is for a dropdown in the HC file. The BOW file doesn't have any named ranges.

The macro given does not hide worksheets. These are all most likely side effects of copying from these protected sheets. My macro actually doesn't check the visibility state of any worksheet, it just renames the sheet and moves it into the master, then closes the original workbook without saving any changes.

As such, whatever the characteristics of the sheet (hidden or visible, etc) should be reflected in the new workbook, too.

Of course you can just add in a line to make sure each sheet is made visible right after it is moved, should take care of that when it happens.

So try this:

Please Login or Register  to view this content.