PDA

View Full Version : Combining All Worksheets of all Workbooks into one Workbook



BravesPiano5
03-20-2015, 09:29 AM
http://www.vbaexpress.com/kb/getarticle.php?kb_id=829#instr

Hello,

I am trying to combine all worksheets (sometimes 5-10 sheets) from all workbooks (40+) into one workbook with multiple sheets. The above code gets stuck here and I'm unable to figure out why:

WS.Copy After:=ThisWB.Sheets(ThisWB.Sheets.Count)

This is the run-time 1004 error I receive:

Method 'Copy' of object'_Worksheet' failed

Would you please help!? :crying:

Thanks!
BravesPiano5

Chis44
03-22-2015, 09:52 AM
I had something similar once. I think it might have been either to do with duplicate tab names or one of the sheets being a chart. I know that is not an exact answer, but hopefully a pointer. I don't have access to the project anymore so can't check the exact nature of my solution to it.

Yongle
03-23-2015, 12:49 AM
I have tried the code and cannot replicate your problem no matter what I do.
Duplicate sheet names etc did not cause a problem - the sheet names were included with a number added (such as Sheet1 (2) )

1 Which version of Excel are you using?
2 Are any worksheets copied?
3 Try not using the functions and running ONLY the subroutine called CombineFiles by hard coding the path into it - I put my test files into D:\documents and amended the code from:

path = GetDirectory
to:

path = "d:\documents"