How do i combine multiple excel workbooks into one vba?

Do you need to combine multiple Excel files? Maybe you have many different Excel workbooks that you're working on, and you want to manage in one place. Perhaps you want one master spreadsheet referencing a few other ones. Maybe you shared a copy of the Excel file with your team, who updated parts of it, and you want to merge it back into the master file.

There are many methods to combine Excel files. Each method can be useful in its own way, depending on your use case. So without further ado, let's go through some of the most popular methods to merge Excel files.

How to Combine Excel Files With Manual Copying?

The easiest and most straightforward way to merge two files is to simply copy the data from one file to another.

1. 1. Select the range to copy or press Ctrl/Cmd + A to select the entire sheet.
2. 2. Press Ctrl/Cmd + C to copy the range.
3. 3. Head to the other spreadsheet and, if necessary, create a new sheet.
4. 4. Select the location to paste the data and press Ctrl/Cmd + V.

While this method might be the fastest when dealing with smaller spreadsheets, it becomes risker and more complicated as the files grow. Human error is almost inevitable, and mistakes are bound to happen. That's why you should only use this method for simple personal use cases.

How to Combine Excel Files Using Move or Copy?

You can use Excel's "Move or Copy" feature to copy one or more Excel sheets from one Workbook to another instantly.

1. 1. Open the Excel files.
2. 2. Select the sheets you want to copy to the other Workbook by holding Ctrl/Cmd and selecting the sheets. The selected sheets will be highlighted. You can unselect sheets by clicking on them again.
3. 3. To select multiple sheets at once, head to the first sheet you want to copy and hold the Shift key. Then, select the last sheet to copy. All the sheets between the two selected ones will be selected as well.
4. 4. Right-click and select "Move or Copy". A dialog box will pop up.

1. 5. Under "To book:", select the other file to move the data to.
2. 6. For "Before sheet:", select where you want the sheets to be inserted. You can always re-organize your sheets later.
3. 7. Press "Ok".

How to Combine Excel Files By Pasting Links?

Excel allows you to reference cells in different Excel files or workbooks. This makes it possible to combine Excel files by referencing them in the master file.

1. 1. Open both Excel files.
2. 2. Copy the first cell from the Workbook you want to reference.
3. 3. Go to your master file.
4. 4. Select the cell where you want to display the data and press right-click.
5. 5. From the menu, select Paste Special > Paste Link. This will automatically generate the necessary formula for you. It will look like this:

='[file.xlsx]sheet'!$A$1

1. 6. To display the other cells, change the formula from absolute cell referencing to relative cell referencing by removing the dollar signs ($) so that it looks like:

='[file.xlsx]sheet'!A1

1. 7. Copy and paste the formula to the other cells that you want to display.

While this method allows you to protect your data by only using references to your files instead of pasting the values, it can be overly complicated. If you break one of the formulas or accidentally delete one of the files, you will end up with many #REF! errors that may be hard to fix.

In addition, when referencing blank cells, while you might think that they would stay empty, the cells will display a zero "0" in the master sheet. There are, of course, workarounds for this to hide the zeros in this case, but it's yet another additional step that makes this method even more complicated.

How to Combine Excel Files Using Power Query?

Power Query (Get & Transform) allows you to import, edit, and consolidate data into Excel. It can also be used to combine multiple Excel files by adding them to one folder:

1. 1. Move all of the files you want to combine into one folder.
2. 2. In Excel, go to the "Data" tab.
3. 3. Press Get Data > From File > From Folder.
4. 4. Browse and select the folder path.
5. 5. Press "Ok".
6. 6. If the files are ready to be combined, press "Combine & Load".
7. 7. If you wish to manipulate the data before combining the files, press the "Transform Data" button. This will open up the Query Editor, where you will have the ability to re-organize and filter data, manage rows and columns, and more.

The Get & Transform method might be one of the easiest to combine files from multiple sources together. Not only does it allow you to import Excel Workbooks, but you can also import other file formats, including text, CSV, XML, JSON, PDF, and more.

However, this feature is only available in all Excel 2016 or later Windows stand-alone versions and Microsoft 365 subscription plans. This means that Mac users are unable to use this feature and instead will need to use VBA.

How to Combine Excel Files Using VBA?

Excel VBA (Visual Basic for Applications) is the programming language for Excel and all Microsoft Office products. It allows you to create macros to manipulate and automate your Excel processes.

Don't worry. You won't have to learn a new programming language. You can use the pre-created macros below. And while we won't be going into details regarding VBA, you can find descriptions for each part of the macros below.

1. 1. Open all the Excel files that you want to combine.
2. 2. To open the VBA editor, use the Windows shortcut Alt + F11 or the Mac shortcut Opt + F11 or Fn + Opt + F11.
3. 3. Go to Insert > Module. This will create a new module for the Workbook.
4. 4. Copy and paste one of the codes below depending on the way you want to combine your spreadsheets:

Combine all Excel files into a new workbook as individual sheets

Sub CombineMultipleFiles()
On Error GoTo eh
    'declare variables to hold the objects required
Dim wbDestination As Workbook
Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim wb As Workbook
Dim sh As Worksheet
Dim strSheetName As String
Dim strDestName As String
    'turn off the screen updating to speed things up
Application.ScreenUpdating = False 'first create new destination workbook
Set wbDestination = Workbooks.Add 'get the name of the new workbook so you exclude it from the loop below
strDestName = wbDestination.Name 'now loop through each of the workbooks open to get the data but exclude your new book or the Personal macro workbook
For Each wb In Application.Workbooks
If wb.Name < > strDestName And wb.Name < > "PERSONAL.XLSB"
Then
Set wbSource = wb
For Each sh In wbSource.Worksheets
sh.Copy After: = Workbooks(strDestName).Sheets(1)
Next sh
End If
Next wb
    'now close all the open files except the new file and the Personal macro workbook.
For Each wb In Application.Workbooks
If wb.Name < > strDestName And wb.Name < > "PERSONAL.XLSB"
Then
wb.Close False
End If
Next wb

    'remove sheet one from the destination workbook
Application.DisplayAlerts = False
Sheets("Sheet1").Delete
Application.DisplayAlerts = True 'clean up the objects to release the memory
Set wbDestination = Nothing
Set wbSource = Nothing
Set wsSource = Nothing
Set wb = Nothing 'turn on the screen updating when complete
Application.ScreenUpdating = False
Exit Sub
eh:
    MsgBox Err.Description
End Sub

Combine all Excel files into a single sheet in a new workbook

Sub CombineMultipleSheets()
On Error GoTo eh
'declare variables to hold the objects required
 Dim wbDestination As Workbook
 Dim wbSource As Workbook
 Dim wsDestination As Worksheet
 Dim wb As Workbook
 Dim sh As Worksheet
 Dim strSheetName As String
 Dim strDestName As String
 Dim iRws As Integer
 Dim iCols As Integer
 Dim totRws As Integer
 Dim strEndRng As String
 Dim rngSource As Range
'turn off the screen updating to speed things up
 Application.ScreenUpdating = False
'first create new destination workbook
 Set wbDestination = Workbooks.Add
'get the name of the new workbook so you exclude it from the loop below
 strDestName = wbDestination.Name
'now loop through each of the workbooks open to get the data
 For Each wb In Application.Workbooks
 If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
 Set wbSource = wb
 For Each sh In wbSource.Worksheets
'get the number of rows and columns in the sheet
 sh.Activate
 ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate
 iRws = ActiveCell.Row
 iCols = ActiveCell.Column
'set the range of the last cell in the sheet
 strEndRng = sh.Cells(iRws, iCols).Address
'set the source range to copy
 Set rngSource = sh.Range("A1:" & strEndRng)
'find the last row in the destination sheet
 wbDestination.Activate
 Set wsDestination = ActiveSheet
 wsDestination.Cells.SpecialCells(xlCellTypeLastCell).Select
 totRws = ActiveCell.Row
'check if there are enough rows to paste the data
 If totRws + rngSource.Rows.Count > wsDestination.Rows.Count Then
 MsgBox "There are not enough rows to place the data in the Consolidation worksheet."
 GoTo eh
 End If
'add a row to paste on the next row down
 If totRws <> 1 Then totRws = totRws + 1
 rngSource.Copy Destination:=wsDestination.Range("A" & totRws)
 Next sh
 End If
 Next wb
'now close all the open files except the one you want
 For Each wb In Application.Workbooks
 If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
 wb.Close False
 End If
 Next wb
'clean up the objects to release the memory
 Set wbDestination = Nothing
 Set wbSource = Nothing
 Set wsDestination = Nothing
 Set rngSource = Nothing
 Set wb = Nothing
'turn on the screen updating when complete
 Application.ScreenUpdating = False
Exit Sub
eh:
MsgBox Err.Description
End Sub

Combine all Excel files into a single worksheet in an active workbook

Sub CombineMultipleSheetsToExisting()
 On Error GoTo eh
'declare variables to hold the objects required
 Dim wbDestination As Workbook
 Dim wbSource As Workbook
 Dim wsDestination As Worksheet
 Dim wb As Workbook
 Dim sh As Worksheet
 Dim strSheetName As String
 Dim strDestName As String
 Dim iRws As Integer
 Dim iCols As Integer
 Dim totRws As Integer
 Dim rngEnd As String
 Dim rngSource As Range
'set the active workbook object for the destination book
 Set wbDestination = ActiveWorkbook
'get the name of the active file
 strDestName = wbDestination.Name
'turn off the screen updating to speed things up
 Application.ScreenUpdating = False
'first create new destination worksheet in your Active workbook
 Application.DisplayAlerts = False
'resume next error in case sheet doesn't exist
 On Error Resume Next
 ActiveWorkbook.Sheets("Consolidation").Delete
'reset error trap to go to the error trap at the end
 On Error GoTo eh
 Application.DisplayAlerts = True
'add a new sheet to the workbook
 With ActiveWorkbook
 Set wsDestination = .Sheets.Add(After:=.Sheets(.Sheets.Count))
 wsDestination.Name = "Consolidation"
 End With
'now loop through each of the workbooks open to get the data
 For Each wb In Application.Workbooks
 If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
 Set wbSource = wb
 For Each sh In wbSource.Worksheets
'get the number of rows in the sheet
 sh.Activate
 ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate
 iRws = ActiveCell.Row
 iCols = ActiveCell.Column
 rngEnd = sh.Cells(iRws, iCols).Address
 Set rngSource = sh.Range("A1:" & rngEnd)
'find the last row in the destination sheet
 wbDestination.Activate
 Set wsDestination = ActiveSheet
 wsDestination.Cells.SpecialCells(xlCellTypeLastCell).Select
 totRws = ActiveCell.Row
'check if there are enough rows to paste the data
 If totRws + rngSource.Rows.Count > wsDestination.Rows.Count Then
 MsgBox "There are not enough rows to place the data in the Consolidation worksheet."
 GoTo eh
 End If
'add a row to paste on the next row down if you are not in row 1
 If totRws <> 1 Then totRws = totRws + 1
 rngSource.Copy Destination:=wsDestination.Range("A" & totRws)
 Next sh
 End If
 Next wb
'now close all the open files except the one you want
 For Each wb In Application.Workbooks
 If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
 wb.Close False
 End If
 Next wb
 
'clean up the objects to release the memory
 Set wbDestination = Nothing
 Set wbSource = Nothing
 Set wsDestination = Nothing
 Set rngSource = Nothing
 Set wb = Nothing
'turn on the screen updating when complete
 Application.ScreenUpdating = False
Exit Sub
eh:
MsgBox Err.Description
End Sub

5. 5. Press the play button. This will open the "Macros" dialogue box.
6. 6. Select the macro and press "Run".

While VBA can be extremely powerful, again, it's a programming language. This means that besides using pre-written macros, you would have to learn Excel VBA to create your own or modify existing ones, which comes with its own set of challenges.

Excel's legacy Shared Workbook feature allows. While Excel eventually replaced it with its new co-authoring feature due to its many limitations, you can still use it to merge shared copies of the same Excel file.

First, you will need to enable highlighting changes:

1. 1. Go to Tools > Track Changes > Highlight changes.
2. 2. Check the "Track changes while editing" box to enable tracking.
3. 3. Select when you want to start highlighting changes, for whom, and for which cell range, if necessary. By leaving the boxes unchecked, you will be tracking all the changes in your Workbook for all collaborators.
4. 4. Select whether you want to enable onscreen highlighting, which outlines affected cells in different colors for different users. This is only useful for workbooks with few changes as it only provides a rudimentary glance at the changes.

Excel's legacy Shared Workbook feature allows. While Excel eventually replaced it with its new co-authoring feature due to its many limitations, you can still use it to merge shared copies of the same Excel file.

First, you will need to enable highlighting changes:

1. 1. Go to Tools > Track Changes > Highlight changes.
2. 2. Check the "Track changes while editing" box to enable tracking.
3. 3. Select when you want to start highlighting changes, for whom, and for which cell range, if necessary. By leaving the boxes unchecked, you will be tracking all the changes in your Workbook for all collaborators.
4. 4. Select whether you want to enable onscreen highlighting, which outlines affected cells in different colors for different users. This is only useful for workbooks with few changes as it only provides a rudimentary glance at the changes.

How to Share an Excel File for Multiple Users?

There are different ways to share an Excel file. Here's how to share an Excel file with multiple users for easy collaboration

READ MORE

To merge two different versions or copies of the same Excel workbook:

1. 1. Go to Tools > Merge Workbooks.
2. 2. Select the Excel file you want to merge with your current one. The Workbook selected must be a copy made from the same shared Workbook and must maintain change history for a sufficient amount of time to be able to merge them.
3. 3. Press "Ok".

The data from the selected version of the spreadsheet will be applied to your current one. If the "Track Changes" feature is turned on, the changes will be highlighted for you to review and choose whether to accept or reject them.

Unfortunately, the Shared Workbook feature has many limitations, which is why it was discontinued by Excel and replaced by their co-authoring feature. The feature lacks support for many of Excel's items and actions like creating or inserting tables, inserting or deleting blocks of cells, deleting worksheets, inserting or changing hyperlinks, and much more.

That's why it's recommended that you use a spreadsheet management platform like Layer.

How to combine multiple Google Sheets into one?

Layer is an add-on that equips finance teams with the tools to increase efficiency and data quality in their FP&A processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

Using Layer, you can:

• Share & Collaborate: Automate your data collection and validation through user controls.
• Automate & Schedule: Schedule recurring data collection and distribution tasks.
• Integrate & Sync: Connect to your tech stack and sync all your data in one place.
• Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Sign up today and get free access to Layer, including all the paid features, so you can start managing, automating, and scaling your FP&A processes on top of Google Sheets!

Can you combine multiple Excel workbooks into one?

How do I combine multiple worksheets into one in VBA?

How do I merge Excel workbooks?

How multiple workbooks work in Excel VBA?