Combining All Worksheets of all Workbooks into one Workbook

[BravesPiano5]

http://www.vbaexpress.com/kb/getarti...b_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!?

Thanks!
BravesPiano5

[Chis44]

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]

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"