Excel workbooks merged

Hi Migle,

Thank you for the forum question.


Yes, it’s definitely possible to merge multiple Excel workbooks without manually copying and pasting the data. You can use a combination of Excel’s built-in features and some VBA (Visual Basic for Applications) scripting to automate this process. Here’s a general approach:

Using Power Query
Open a new Excel workbook.
Go to the Data tab and select Get Data > From File > From Folder.
Browse to the folder where your workbooks are stored and select it.
Power Query Editor will open, showing a list of all files in the folder. Click Combine > Combine & Load.
Select the sheet or range you want to combine from each workbook.
Power Query will combine the data from all the workbooks into a single table.
Using VBA
If you prefer using VBA, here’s a simple script to get you started:

Sub MergeWorkbooks()
Dim FolderPath As String
Dim Filename As String
Dim Sheet As Worksheet
Dim DestSheet As Worksheet
Dim LastRow As Long
Dim ws As Worksheet

' Set the folder path
FolderPath = "C:\Path\To\Your\Folder\"

' Create a new workbook for the merged data
Set DestSheet = Workbooks.Add(xlWBATWorksheet).Sheets(1)

' Loop through each file in the folder
Filename = Dir(FolderPath & "*.xlsx")
Do While Filename <> ""
' Open the workbook
Set wb = Workbooks.Open(FolderPath & Filename)

' Loop through each sheet in the workbook
For Each Sheet In wb.Sheets
' Find the last row in the destination sheet
LastRow = DestSheet.Cells(DestSheet.Rows.Count, "A").End(xlUp).Row + 1

' Copy the data to the destination sheet
Sheet.UsedRange.Copy DestSheet.Cells(LastRow, 1)
Next Sheet

' Close the workbook
wb.Close False

' Get the next file
Filename = Dir
Loop
End Sub

Steps to Run the VBA Script
Press Alt + F11 to open the VBA editor.
Insert a new module by clicking Insert > Module.
Copy and paste the script into the module.
Press F5 to run the script.
This script will loop through all Excel files in the specified folder, copy the data from each sheet, and paste it into a new workbook.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector