Results 1 to 3 of 3

Thread: Combining All Worksheets of all Workbooks into one Workbook

  1. #1

    Combining All Worksheets of all Workbooks into one Workbook


    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!?


  2. #2
    VBAX Newbie
    Mar 2015
    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.

  3. #3
    VBAX Mentor
    Feb 2015
    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
    path = "d:\documents"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts