ExcelInsider Team
Manually copying and pasting data from multiple worksheets into a single master sheet can be a time-consuming process, especially when dealing with a large number of sheets. Fortunately, Excel’s Visual Basic for Applications (VBA) allows you to automate this process with a simple script. In this article, we will guide you through creating and running VBA code to merge multiple sheets into one, both row-wise and column-wise.
To merge multiple sheets into one using VBA:
➤ Go to Developer > Visual Basic to open the VBA editor.
➤ Insert a new module and paste the merging code.
➤ Press the Run button or use F5 to execute the macro and merge the sheets column-wise.
Merging Multiple Sheets Row-wise into One Sheet with VBA
Sometimes you have data spread across many sheets. To combine them row-wise involves placing the data in rows one after another. In this guide, we will automate the entire process using a simple VBA macro.
Suppose we have three separate sheets named “North Sales,” “South Sales,” and “East Sales”, each containing sales data for different regions. Our goal is to merge all of this data into a new, single worksheet row-wise.
Here is what our sample data looks like:
North Sales Sheet: Contains a list of products and their total sales.
South Sales Sheet: Contains a different set of products and their total sales.
East Sales Sheet: Also contains a unique set of products and total sales figures.
Now, let’s open the VBA editor to write our code.
➤ Go to the Developer tab.
➤ Click on Visual Basic.
This will open the Microsoft Visual Basic for Applications window. In the VBA editor, do the following:
➤ Insert a new module by going to Insert > Module.
➤ Paste the following code and hit Run.
Sub Merge_North_South_East_Row_wise()
Dim sourceSheets As Variant
sourceSheets = Array("North Sales", "South Sales", "East Sales")
Dim mergedSheet As Worksheet
Set mergedSheet = Worksheets.Add
mergedSheet.Name = "Merged Sheet"
Dim ws As Worksheet, rng As Range
Dim nextRow As Long
nextRow = 1
Dim i As Integer
For i = LBound(sourceSheets) To UBound(sourceSheets)
Set ws = Worksheets(sourceSheets(i))
Set rng = ws.UsedRange
rng.Copy Destination:=mergedSheet.Cells(1, nextRow)
nextRow = nextRow + rng.Columns.Count + 1
Next i
End Sub➧ Set mergedSheet = Worksheets.Add(): This creates a new worksheet named "Merged Sheet" where our combined data will be placed.
➧ nextRow = 1: We initialize a counter to keep track of the starting row for each sheet data.
➧ For Each ws In Worksheets(sourceSheets): This loop iterates through each of the sheets we specified in our array.
➧ rng.Copy Destination:=: This line copies the used range of each sheet and pastes it into the "Merged Sheet," starting at the nextRow.
➧ nextRow = nextRow + rng.Rows.Count: After each sheet is pasted, the next starting row is calculated, ensuring data from the next sheet is appended below the previous one.
As a result, a new worksheet named “Merged Sheet” will be created. All the data from your source sheets will be stacked one after another, resulting in a single list.
Merging Multiple Sheets Column-wise Into One Sheet with VBA
If you want to merge your data to create a wide table with all the data in columns, you can use a different VBA code.
➤ Following the previous method, go to Developer > Visual Basic.
➤ In the VBA editor, insert another module by clicking Insert > Module.
➤ Paste the following code and hit Run.
Sub Merge_North_South_East_column_wise()
Dim sourceSheets As Variant
sourceSheets = Array("North Sales", "South Sales", "East Sales")
Dim mergedSheet As Worksheet
Set mergedSheet = Worksheets.Add
mergedSheet.Name = "Merged Sheet"
Dim ws As Worksheet, rng As Range
Dim nextCol As Long
nextCol = 1
Dim i As Integer
For i = LBound(sourceSheets) To UBound(sourceSheets)
Set ws = Worksheets(sourceSheets(i))
Set rng = ws.UsedRange
If i = 0 Then
rng.Copy Destination:=mergedSheet.Cells(nextCol, 1)
Else
rng.Offset(1).Resize(rng.Rows.Count - 1).Copy Destination:=mergedSheet.Cells(nextCol, 1)
End If
nextCol = mergedSheet.Cells(mergedSheet.Rows.Count, 1).End(xlUp).Row + 1
Next i
End Sub➧ Set mergedSheet = Worksheets.Add: This command creates a brand new sheet in your workbook.
➧ mergedSheet.Name = "Merged Sheet": This names the new sheet so you know where to find your final, merged data.
➧ rng.Offset(1).Resize(rng.Rows.Count - 1).Copy: This part copies all the data from the sheet but skips the first row, which is usually the header. This prevents you from having duplicate headers in your final merged sheet.
➧ nextCol = mergedSheet.Cells(mergedSheet.Rows.Count, 1).End(xlUp).Row + 1: This line of code finds the next empty row. It looks at the last filled cell in column A of the new sheet, and then sets the next pasting point to the row right below it. This ensures that each sheet data is placed directly after the previous one.
This way, a new worksheet named “Merged Sheet” will be created. The data from all the source sheets will be merged one after another. Here, the headers from the first sheet are kept, resulting in a single list.
Sometimes, you might want a blank row to separate each set of data for better visual clarity. You can easily achieve this with a small modification to the code.
➤ In the VBA editor, create a new module, paste this code, and hit Run.
Sub Merge_North_South_East_RowWise_WithGap()
Dim sourceSheets As Variant
sourceSheets = Array("North Sales", "South Sales", "East Sales")
Dim mergedSheet As Worksheet
Set mergedSheet = Worksheets.Add
mergedSheet.Name = "Merged Sheet"
Dim ws As Worksheet, rng As Range
Dim nextCol As Long
nextCol = 1
Dim i As Integer
For i = LBound(sourceSheets) To UBound(sourceSheets)
Set ws = Worksheets(sourceSheets(i))
Set rng = ws.UsedRange
rng.Copy Destination:=mergedSheet.Cells(nextCol, 1)
nextCol = nextCol + rng.Rows.Count + 1
Next i
End Sub
As a result, a new worksheet named “Merged Sheet” will be created. All the data from your source sheets will be placed one after another, with a blank row in between each dataset.
Frequently Asked Questions
How do I avoid repeating the header row when merging sheets?
You can skip the header from the second sheet onward using Offset(1) and Resize so only the first header is copied.
Will formulas in the original sheets be preserved in the merged sheet?
No. When you copy the data using .Copy Destination:=…, it pastes only values, not formulas. If you want to preserve formulas, you will need to copy entire cells without converting them to values.
Can I automate this VBA to run whenever I open the workbook?
Yes. You can place the macro in the Workbook_Open() event inside the ThisWorkbook module to run automatically when the file opens.
Concluding Words
Above, we have explored all the ways to merge multiple sheets into one sheet using VBA code. Whether you need to merge data row-wise or column-wise, a simple macro can handle the heavy lifting for you. By following the steps above, you can customize the code to combine your specific data. If you have any questions or need further assistance, feel free to leave a comment below.
