Tom has attended:
Excel Advanced course
Excel Advanced course
Combining multiple workbooks
Hello,
I am trying to combine 30 workbooks into one workbook automatically without having to copy and paste loads of times.
I would like each workbook to become one tab in the new workbook.
So I would like to end up with one workbook with 30 tabs of data in it, does this make sense?
RE: Combining multiple workbooks
Hi Tom
A quick way would be to write a macro. Is that the approach you want to take? If you are going to repeat the process of copying the workbook data it may be worth going down the macro route.
Please let me know Tom then I can write an example of how you could acieve this with a macro.
Regards
Doug Dunn
Best STL
RE: Combining multiple workbooks
Hello Doug,
Yes a Macro would be good, I take it to automatically copy each worksheet into tab, would it have to take into account different sized data sets, e.g some tables having more rows in them than others??
RE: Combining multiple workbooks
Hi Tom,
Each workbook can have different numbers of rows of data. It may be best to send an example of 2 or 3 of your workbooks or someting similar.
When replying you can click on the Upload link.
Doug
RE: Combining multiple workbooks
Hi Tom
Just checking. Do you still want help with combining multiple workbooks? I can send you a macro example if you do.
Doug
Best STL
RE: Combining multiple workbooks
Here's a simple example of a macro you might want to adapt.
The data from 4 workbooks called North, South, East and West is copied into 4 different tabs in a workbook called Sales all Regions.xlsm
You can make it work for your example by changing the file path where workbooks are opended and changing the names of files and tabs to your ones.
Asumptions:
This example assumes all tables start at A1 but can have variable numbers of rows and columns.
There are no blank rows or columns.
Empty worksheet tabs are already made in the consolidated workbook (Sales all regions).
Hope this helps.
Sub CopyData()
'
' CopyData Macro
' This macro Opens the North, Souh, East and West workbooks one at a time and copies the data into the coresponding tab in Sales all regions workbook.
'
' Get North data
Workbooks.Open Filename:="z:\Practice Files\North.xlsx"
Range("A1").Select
Range("A1").CurrentRegion.Select
Selection.Copy
ActiveWindow.Close
Windows("Sales all Regions.xlsm").Activate
Sheets("North").Select
Range("A1").Select
ActiveSheet.Paste
'Get South data
Workbooks.Open Filename:="z:\Practice Files\South.xlsx"
Range("A1").Select
Range("A1").CurrentRegion.Select
Selection.Copy
ActiveWindow.Close
Windows("Sales all Regions.xlsm").Activate
Sheets("South").Select
Range("A1").Select
ActiveSheet.Paste
'Get East data
Workbooks.Open Filename:="z:\Practice Files\East.xlsx"
Range("A1").Select
Range("A1").CurrentRegion.Select
Selection.Copy
ActiveWindow.Close
Windows("Sales all Regions.xlsm").Activate
Sheets("East").Select
Range("A1").Select
ActiveSheet.Paste
'Get West data
Workbooks.Open Filename:="z:\Practice Files\West.xlsx"
Range("A1").Select
Range("A1").CurrentRegion.Select
Selection.Copy
ActiveWindow.Close
Windows("Sales all Regions.xlsm").Activate
Sheets("West").Select
Range("A1").Select
ActiveSheet.Paste
MsgBox "All workbook data copied into worksheets"
End Sub
