Combine/Merge Multiple workbooks/sheets

  • Hello, New here. Hope Im in the right place.
    I found a thread that seems to be same problem I am having. I tried using the code provided to other member and am NOT getting results. Here is what I need.
    I have 500 workbooks, all with same header and sheet names.
    I need to combine the content all into a single workbook with all sheets merged into workbook. data all starts at A2 and varies in row content from 1 row to 5000 per workbook/sheet.
    The code is what I found on this site and tried to modify to work before I asked the question.


    Option Explicit


    Sub Extract_Files()



    Application.ScreenUpdating = False



    Dim ParentFolder As String
    Dim WS As Worksheet
    Dim ThisWB As Workbook
    Dim ThisWS As Worksheet
    Dim OtherWB As Workbook
    Dim WScount As Integer
    Dim File As Variant



    Set ThisWB = ActiveWorkbook
    Set ThisWS = ActiveSheet



    ParentFolder = "E:\TestMergeVAI\test" 'Change as required


    If Right(ParentFolder, 1) <> "" Then ParentFolder = ParentFolder & ""


    File = Dir(ParentFolder)



    WScount = 0



    While File <> ""


    Set OtherWB = Workbooks.Open(ParentFolder & File)


    For Each WS In OtherWB.Sheets


    If Left(UCase(WS.Name), 4) = "Step" Then
    WScount = WScount + 1


    If WScount = 1 Then WS.Rows(1).EntireRow.Copy Destination:=ThisWS.Rows(1).EntireRow


    WS.Range("A2:N" & Range("A" & WS.Rows.Count).End(xlUp).Row).Copy
    ThisWS.Range("A" & ThisWS.Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False


    End If


    Next WS


    OtherWB.Close False


    Set OtherWB = Nothing



    File = Dir



    Wend



    ThisWB.Activate
    ThisWS.Activate



    Set ThisWB = Nothing
    Set ThisWS = Nothing



    Application.ScreenUpdating = True



    MsgBox WScount & " worksheets transferred successfully.", vbInformation, "Done"





    End Sub



    When I run the code. I get a window that says 0 book combined


    Hope you all can help

  • Try this macro. Change the sheet name where indicated in the code to the name of your destination sheet. Also, the macro assumes that your source files have an "xlsx" extension. Change that to suit your needs as well.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Let me explain a bit of my project if it helps. I am a web designer doing a massive 100k item data migration. I have a master excel sheet template for uploading items to my web site. That file consists of 8 tabs labeld 1 - 8 9 Step 1 Step 2 ... etc.


    Step 1 would be categories
    Step 2 would be Sub Cats
    Step3 Items to upload
    Step 4 Images
    ... ETC...
    I have 500 individual XLS files Templates with dif cat and sub cats and items
    I need to bring it all together to make a MASTER UPLOAD xls with all Cats listed in order in tab called Step 1 - All Items Listed together in Step 3 and so on.

  • Hello,
    I was able to get a partial result. It is only merging the first tab in all the workbooks. It does not move to next tab and combine. The result of tab1 is what I am looking for.
    Another NOTE: the data range is different per sheet.


    Sheet1= A2:AP
    Sheet2=A2:X
    Sheet3=A2:E
    Sheet4=A2:BM
    Sheet5=A2:M
    Sheet6=A2:F
    Sheet7=A2:F
    Sheet8=A2:F


    and vary in ROWS from 2 to 1000 row entries.

  • To clarify, you want all the Step 1 sheets in the source files combined into the Step 1 sheet in the Master,

    [SIZE=13px]all the Step 2 sheets in the source files combined into the Step 2 sheet, etc. This would mean that each "Step*" sheet in the Master would contain the data from the corresponding sheet in the 500 source files. Is this correct? Can I assume that the 8 "Step*" sheets already exist in the Master? Are the 500 workbooks the only files in the "E:\TestMergeVAI\test" folder? Is the [/SIZE][SIZE=13px]MASTER UPLOAD in the same folder?[/SIZE]

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Give this a try:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • My apologies. I forgot to delete a test line of code and another line was not in the right location. Try this version.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Ok, sorry for the delay. Was testing a strange result.
    I took 7 files to combine.



    it seems to be skipping data from one file and 1 of the 8 sheets. Very Strange.
    All other information for that file is being combined. It is skipping step 4.


    I checked the file and there is data in that sheet.



    The VBA does seem to work other than that,.

  • Without working with the actual files that aren't working properly, it is difficult to identify the problem.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • This is so strange.
    I leave the one file in the test folder and it works with no problem. All sheets copy over.
    When I add another file to the test folder, it merges all the workbooks and sheets except for 1 sheet data.


    That same wrk book and sheet load when alone in folder.


    never seen anything like this.


    Any Ideas????

  • I think that I've found the problem. With the C4 file, do the following:
    For each "Step*" sheet, select cell A1 and then while holding down the CRTL key, press the END key. This will take you to the last used cell in that sheet. You will notice that for some of the sheets, it takes you well below the last used cell.
    -

    [SIZE=13px]In the "Step 1" sheet, it takes you down to row 2.
    -In the "Step 4" sheet, it takes you down to row 1000.[/SIZE]


    [SIZE=13px]-In the "Step 6" sheet, it takes you down to row 1000.[/SIZE]


    [SIZE=13px]-In the "Step 7" sheet, it takes you down to row 65.[/SIZE]


    [SIZE=13px]-In the "Step 8" sheet, it takes you down to row 397.


    Sheets 2,3 and 5 all take you to the actual last row so they are OK. Delete all the extra rows on each sheet after the actual last used row and save the file. Close it and re-open it. If you [/SIZE][SIZE=13px]hold down the CRTL key, press the END key [/SIZE][SIZE=13px]in each sheet again, you will see that it now takes you to the actual last used row. When you said that it didn't work for some of the sheets, it actually did work but the data for those sheets was pasted after the last row, which in sheet "Step4*" was 1001. If you scrolled down after you ran the macro, you would have seen the data. Another problem was that column A in some of the sheets had no data. This problem has also been fixed.[/SIZE]



    [SIZE=13px]You will have to check all your source files in the same way and delete all the extra rows.


    Use this revised macro:[/SIZE]






    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!