Merge Data from Worksheets into Master Worksheet

How to merge data from different worksheets into a master worksheet with Excel VBA. A user may want to create a report from data in cells in many worksheets by creating a new master worksheet. We describe here a process on how to create the master worksheet automatically with data from multiple worksheets. The formatting of the data in the different worksheets is maintained in the master worksheet. Watch the training video before studying the details of the VBA code:

Watch this video on YouTube.

Here’s the complete VBA code:

Sub MergeDataFromWorksheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim erow As Long, lrowsh As Long, startrow As Long
Dim CopyRng As Range
startrow = 2
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

‘Delete the sheet “MyMergeSheet”
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets(“MyMergeSheet”).Delete
On Error GoTo 0
Application.DisplayAlerts = True

‘Add a worksheet with the name “MyMergeSheet”
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = “MyMergeSheet”
‘loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> DestSh.Name Then
‘Find the next blank or empty row on the DestSh
erow = DestSh.Range(“A” & Rows.Count).End(xlUp).Offset(1, 0).Row
‘Find the last row with data in the Sheet
lrowsh = sh.Range(“A” & Rows.Count).End(xlUp).Row

Set CopyRng = sh.Range(sh.Rows(startrow), sh.Rows(lrowsh))

‘copies values/formats
CopyRng.Copy
With DestSh.Cells(erow, 1)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

End If
Next

DestSh.Cells(1, 1) = “Scrip Name”
DestSh.Cells(1, 2) = “Qty”
DestSh.Cells(1, 3) = “Buy Avg Rate”
DestSh.Cells(1, 4) = “20%”
DestSh.Cells(1, 5) = “15%”
DestSh.Cells(1, 6) = “12%”
DestSh.Cells(1, 7) = “Last date of purchase”
‘AutoFit the column width in the DestSh sheet
DestSh.Columns.AutoFit

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Further reading:

Merge cells from all or some worksheets into one Master sheet