Combining Multiple workbook into single Workseet.

[zeecharle]

Anyone help me combine multiple workbook containing worksheet name "SUMMARY" into a single worksheet.

Please see the link below to understand more what I am saying here.

Thank you in advance.

For GENERAL SUMMARY(EXPECTED)

-Zee

 

[svendiamond]

Do you want to copy just down to the last row used? Like for PART 1 do you just want cells B4:G20 copied? Since there are no more products below that?

 

[svendiamond]

Remember to save your work before running any code. Then, try this:

Sub copyParts()


Dim partNum As Integer, myBook As Workbook, mySheet As Worksheet, lastRow As Long, firstBlank As Long


Set myBook = Workbooks("GENERAL PARTS LIST")
Set mySheet = myBook.Sheets("GENERAL SUMMARY")

'I only put this part in to make sure the main sheet is clear first
mySheet.Range("B4:K1000").ClearContents


For partNum = 1 To 3
    With Workbooks("PART " & partNum).Sheets("SUMMARY")
        lastRow = .Range("B" & Rows.Count).End(xlUp).Row
        firstBlank = mySheet.Range("B" & Rows.Count).End(xlUp).Row + 1
    
        .Range("B4:G" & lastRow).Copy Destination:=mySheet.Range("B" & firstBlank)
    End With
Next partNum


End Sub

 

[AlphaFrog]

There is a free add-in that can do what you're asking.

RDBMerge, Excel Merge Add-in for Excel

 

[zeecharle]

Remember to save your work before running any code. Then, try this:

Sub copyParts()Dim partNum As Integer, myBook As Workbook, mySheet As Worksheet, lastRow As Long, firstBlank As LongSet myBook = Workbooks("GENERAL PARTS LIST")Set mySheet = myBook.Sheets("GENERAL SUMMARY")'I only put this part in to make sure the main sheet is clear firstmySheet.Range("B4:K1000").ClearContentsFor partNum = 1 To 3    With Workbooks("PART " & partNum).Sheets("SUMMARY")        lastRow = .Range("B" & Rows.Count).End(xlUp).Row        firstBlank = mySheet.Range("B" & Rows.Count).End(xlUp).Row + 1            .Range("B4:G" & lastRow).Copy Destination:=mySheet.Range("B" & firstBlank)    End WithNext partNumEnd Sub

Sir svendiamond,

Please consider that if I add an another excel file named "PART 4", it should add another column in the "GENERAL SUMMARY". Also the file name of the workbook is not "PART ". It is just an example. It contains different name but same format inside the workbook.

The important part is that I should combine/merge/collaborate the multiple workbook that has the worksheet named "SUMMARY" into 1 single worksheet "GENERAL SUMMARY" and the "QUANTITY" should be seperated in different columns labeled by the workbook file name.

And when I run the code nothing happens.

-Zee

 

[zeecharle]

There is a free add-in that can do what you're asking.

RDBMerge, Excel Merge Add-in for Excel

Sir AlphaFrog,

I need a macro code for this,

But Thanks for the Add-In.

Why I need a macro code? Becuase some of the computer here my workare does not have Microsoft Office installed on it. Some are just a free office software like OpenOffice, Kingsoft, etc.

So I need a macro code for this.

Thank you for your time in sharing.

-Zee

 

[AlphaFrog]

Here are some code example you may find useful.

Merging Data from Multiple Workbooks into a Summary Workbook in Excel