Combining data from multiple workbooks into one master workbook using a macro

[catzeyes]

I have 3 workbooks in Excel 2010 that are identical. I have created one master file that is in exactly the same format. All of these files have multiple tabs but I need to collate the information in the master workbook from 3 of the tabs in each of the original workbooks. How can I use a macro to take the data from all 3 workbooks and from each of the 3 tabs and put into the relevant tabs on the master workbook to create the complete list of data? I am very new to macros so any help is much appreciated!

 

[JLGWhiz]

The code below will copy from three worksheets in each of three workbooks and paste the contents into a single worksheet in the master workbook. It assumes the code runs from the master workbook code module. The code also assumes that all four workbooks are open with the master being opened first. The first workbook opened is Workbooks(1), the othere will be (2), (3) and (4). If there are more than three sheets per workbook, you will need to substitute those names where in is noted in the code to edit the sheet name.

Sub collate()
Dim wb As Workbook, sh As Worksheet, sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, lr As Long, shAry
Set sh = ThisWorkbook.Sheets(1)
    For i = 2 To 4
        Set sh1 = Workbooks(i).Sheets(1) 'Edit sheet name
        Set sh2 = Workbooks(i).Sheets(2) 'Edit sheet name
        Set sh3 = Workbooks(i).Sheets(3) 'Edit sheet name
        shAry = Array(sh1, sh2, sh3)
            For j = LBound(shAry) To UBound(shAry)
                lr = shAry(j).UsedRange.Rows.Count
                shAry(j).Range("A2:A" & lr).EntireRow.Copy _
                sh.Cells(Rows.Count, 1).End(xlUp)(2)
            Next
    Next
End Sub

Better code could be provided if better details were given.

 

[sujon]

What is mean "past the contents"

 

[catzeyes]

Thanks for the code. Let me elaborate on the issue. I have three identical workbooks which contain 8 tabs. Out of these 8 tabs I wish to take the data from 3 of these tabs and from multiple columns which are not in a consecutive order in each of the tabs. The master file that I want the data to go into is in exactly the same format as each of these 3 workbooks and I need to the data to go from tab A (from each of the 3 workbooks) into tab A into the master and from tab B into tab B of the master and from tab C into tab C of the master. Please let me know if you require further details to help with the code. Thanks

 

[JLGWhiz]

See if this will do it. If not, you will need to be more forthcoming about your sheet names, if they are not the first three in each workbook.

Sub collate2()
Dim wb As Workbook, sh As Worksheet, sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, lr As Long, shAry
    For i = 2 To 4
        Set sh1 = Workbooks(i).Sheets(1) 'Edit sheet name
        Set sh2 = Workbooks(i).Sheets(2) 'Edit sheet name
        Set sh3 = Workbooks(i).Sheets(3) 'Edit sheet name
        shAry = Array(sh1, sh2, sh3)
            For j = LBound(shAry) To UBound(shAry)
        Set sh = ThisWorkbook.Sheets(j + 1)
                lr = shAry(j).UsedRange.Rows.Count
                shAry(j).Range("A2:A" & lr).EntireRow.Copy _
                sh.Cells(Rows.Count, 1).End(xlUp)(2)
            Next
    Next
End Sub

 

[catzeyes]

The tab names are 'Data, 'Budget and 'Q2RF'. They are not the first 3 tabs in the workbook but rather they are 4th,5th and 6th tabs into the workbook. Does this help?

 

[JLGWhiz]

The tab names are 'Data, 'Budget and 'Q2RF'. They are not the first 3 tabs in the workbook but rather they are 4th,5th and 6th tabs into the workbook. Does this help?

The sheet names have how been added in so that they are represented by the three sheet variables, which should ensure that the correct data is copied over. That is about it for me. Regards, JLG

Sub collate3()
Dim wb As Workbook, sh As Worksheet, sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, lr As Long, shAry
    For i = 2 To 4
        Set sh1 = Workbooks(i).Sheets("Data") 'Edit sheet name
        Set sh2 = Workbooks(i).Sheets("Budget") 'Edit sheet name
        Set sh3 = Workbooks(i).Sheets("Q2RF") 'Edit sheet name
        shAry = Array(sh1, sh2, sh3)
            For j = LBound(shAry) To UBound(shAry)
        Set sh = ThisWorkbook.Sheets(j + 1)
                lr = shAry(j).UsedRange.Rows.Count
                shAry(j).Range("A2:A" & lr).EntireRow.Copy _
                sh.Cells(Rows.Count, 1).End(xlUp)(2)
            Next
    Next
End Sub

 

[chris priyesh]

Hi Mr JLGWhiz,
I was trying to use your code but i had a problem,
data from all the 3 sheets is being copied into the sheet 1 of master file.
Is there a way where in which the each sheet of master file will contain the merged data of the 3 work books but of the respective sheets?
i.e., sheet 1 of master sheet would contain the data from sheet 1 of workbooks 1,2,3 etc.

Any help or suggestion would help me a lot and would be greatly appreciated.

Thanks
Chris

See if this will do it. If not, you will need to be more forthcoming about your sheet names, if they are not the first three in each workbook.

Sub collate2()
Dim wb As Workbook, sh As Worksheet, sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, lr As Long, shAry
    For i = 2 To 4
        Set sh1 = Workbooks(i).Sheets(1) 'Edit sheet name
        Set sh2 = Workbooks(i).Sheets(2) 'Edit sheet name
        Set sh3 = Workbooks(i).Sheets(3) 'Edit sheet name
        shAry = Array(sh1, sh2, sh3)
            For j = LBound(shAry) To UBound(shAry)
        Set sh = ThisWorkbook.Sheets(j + 1)
                lr = shAry(j).UsedRange.Rows.Count
                shAry(j).Range("A2:A" & lr).EntireRow.Copy _
                sh.Cells(Rows.Count, 1).End(xlUp)(2)
            Next
    Next
End Sub