Consolidating many workbooks with multiple tabs into one master workbook with multiple tabs

[germanexcel]

Dear community,
I have been trying to come up with a macro to help shorten the hours of manual entry (copy, paste, copy, paste) that the following task requires—unfortunately, I just can’t get it correct.
Here is the background and what I need it to do:
I have about 300 workbooks. Each workbook contains 5 tabs. Each tab has a varying amount of columns with relevant data beneath. The task is to take the data stored in these workbooks and consolidate it into one master workbook. The master workbook should have the same 5 tabs (identical in name as well as column header etc.) and the respective data should be aggregated here. This master workbook is then simply a consolidation of all the data from the other 300 workbooks.
Is this possible? Any help would be really appreciated!!
Best regards,
Germanexcel

 

[JLGWhiz]

This code assumes that your 300 workbooks are in the same directory as the master workbook, and that these are the only Excel workbooks in that directory. If this assumtion is not true, the code fails. Also assumed is that the code will run from the Master Workbook

Sub consWB()
Dim wb As Workbook, sh As Worksheet, lr As Long, lc As Long, fName As String, fPath As String
fPath = ThisWorkbook.Path
If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
fName = Dir(fPath & "*.xl*")
    Do While fName <> ""
        If fName <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(fPath & fName)
            For Each sh In wb.Sheets
                lr = sh.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
                lc = sh.Cells.Find("*", , xlFormulas, xlPart, xlByColumns, xlPrevious).Column
                sh.Range("A2", sh.Cells(lr, lc)).Copy ThisWorkbook.Sheets(sh.Name).Cells(Rows.Count, 1).End(xlUp)(2)
            Next
        End If
        ThisWorkbook.Save
        wb.Close False
        fName = Dir
    Loop
End Sub

Copy this code to your standard code module 1. To run the code from the Excel screen, press Alt + F8, then double click the macro name.

 

[germanexcel]

thanks a ton, JLGWhiz. This code does exactly what I was trying to get Excel to do. There is one thing that I am interested to know: the code opens up each of the 300 workbooks, extracts all the data, copies it to the master then opens up the next workbook and repeats the process--is there a way to have each workbook closed after the data has been extracted and copied to the master workbook? I ran into the Problem that Excel just crashed after a while with so many workbooks open.

 

[JLGWhiz]

It should have been closing the workbooks. I suspect the saving of the master is causing a timing problem and results in the close command being skipped over. Try this rivised version where the master will only be saved after all the copying is done.

Sub consWB2()
Dim wb As Workbook, sh As Worksheet, lr As Long, lc As Long, fName As String, fPath As String
fPath = ThisWorkbook.Path
If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
fName = Dir(fPath & "*.xl*")
    Do While fName <> ""
        If fName <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(fPath & fName)
            For Each sh In wb.Sheets
                lr = sh.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
                lc = sh.Cells.Find("*", , xlFormulas, xlPart, xlByColumns, xlPrevious).Column
                sh.Range("A2", sh.Cells(lr, lc)).Copy ThisWorkbook.Sheets(sh.Name).Cells(Rows.Count, 1).End(xlUp)(2)
            Next
        End If
        wb.Close False
        fName = Dir
    Loop
    ThisWorkbook.Save
End Sub

 

[germanexcel]

Now it seems to be creating another failure elsewhere. I receive this message "object variable or with-block variable not set" at the 'wb-close False' line in the code

 

[JLGWhiz]

Now it seems to be creating another failure elsewhere. I receive this message "object variable or with-block variable not set" at the 'wb-close False' line in the code

I cannot duplicate the error. It runs as intended for me. Try deleting the code from your workbook, then re-copy the revised version from the thread and see if it will give the same error.

If you continue to receive the error, then open the VB editor and use function key F8 to step through the code one line at a time. As the highlight passes over the 'Set wb =', hover the mouse pointer over the 'wb' and see the tool tips shows a value of 'Nothing'. If it does, then we have to figure out why it is doing that.

 

[germanexcel]

I am not completely sure why it was producing that error to be honest... But I deleted the code from the workbook and re-entered it and it ran as intended. Thanks again for the help with this! I am going to go through line by line and try and decipher just how you wrote it. Like I said, I'm a newbie at this. greetings from Germany.

 

[JLGWhiz]

I am not completely sure why it was producing that error to be honest... But I deleted the code from the workbook and re-entered it and it ran as intended. Thanks again for the help with this! I am going to go through line by line and try and decipher just how you wrote it. Like I said, I'm a newbie at this. greetings from Germany.

Glad it is working for you.
regards, JLG