Copying different excel files in a folder to one excel file as sheets

Try this:

Please Login or Register  to view this content.

This macro can be run and it will update an existing report, too, clearing all the old entries and creating an entirely new compilation report from the noted folder.

Thanks JBeaucaire for your reply,I tried with the macro what you sent but getting error...out of range run-time script 9 like that....

Please add the following line ABOVE my macros title line:

Please Login or Register  to view this content.

Then, while still in the VBA editor, click on Debug > Compile VBAProject. See if the debugger points out any visible problems with your edits in advance.

Then run the macro again and when the error pops up (if it does again), be sure to select DEBUG so that it will open the editor and highlight the line of code it is failing on.

One simple idea, any chance you forgot the final "\" when editing the strPath?

I ran the module I wrote you multiple times and it seems to work fine, but my sheets are EXACTLY the way you described. Any chance your original specifications were off somehow? Is the sheet needed inside each workbook REALLY called by the exact same name as the workbook plus .xdo?

You could post a sanitized version of one of the import sheets so I can look at the layout.


BTW, your macro is pretty much the same exact approach mine uses, you've just got different variable names. It's the same critter.

Can you zip the file(s) and not rar files.

The error on the line you've noted means the sheetname does not exist in the workbook that opened. Is that the letter "o" or the number "0" on there? .xdo and xd0 are different things entirely. Verify your sheetnames are what you think they are, the macro works because my sample sheets all have "A.xdo", "B.xdo" sheets in them...

As per our Skype conversation, I have removed the "xdo" activation step since each workbook being opened only has one sheet in it, this is an unnecessary step.

Please Login or Register  to view this content.

I highlighted the updated lines...