June 23, 2016

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
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
With DestSh.Cells(erow, 1)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

End If

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

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

Further reading:

Merge cells from all or some worksheets into one Master sheet

7 thoughts on “Merge Data from Worksheets into Master Worksheet

  1. Dear Dinesh,
    I need your help VBA.
    I have many excel files in folder. Files have many similar worksheets but different data and I want to extract data from one specific worksheet name “statistics” which is present in each file. Next I want to put the data in one master worksheet. The data range is A1:R10. Can you please help me with VBA that I can use for this case.
    Thank you

  2. hello sir,
    thanx for your such a wonder full work…

    actually I have a problem ,my work sheet not show visual basic code which I merge these 3 sheets
    kindly guide In this matter

  3. Hello Dinesh,

    This is very useful information. Very systematically explained. Very easy to understand. I tried this and it worked for me in first attempt without any error except single quote and double quote replacement.

    I was looking for similar lines a solution for my problem. Your example copies all rows from each sheet into new sheet one after other. My requirement is copy first 2 columns, then 3rd column the sheet name and then 4th column will be the 3rd column from respective sheet.

    A11 : GANESH
    A13 : 5

    A11 : SIDDESH
    A13 : 10

    Where CITY1 and CITY2 are 2 sheets
    Now when I run a macro then new sheet will be created say ‘MergedSheet’

    A13: CITY1 CITY2
    A14: 5 10

Comments are closed.