Combine data from multiple sheets into One

Home » EXCEL » Combine data from multiple sheets into One
December 9, 2013 EXCEL No Comments

Sub MergeSheets()

‘ Appends data from all the selected worksheets onto the end of the
‘ active worksheet.

Const NHR = 1 ‘Number of header rows to not copy from each MWS

Dim MWS As Worksheet ‘Worksheet to be merged (appended)
Dim AWS As Worksheet ‘Worksheet to which the data are transferred
Dim FAR As Long ‘First available row on AWS
Dim LR As Long ‘Last row on the MWS sheets

Set AWS = ActiveSheet

For Each MWS In ActiveWindow.SelectedSheets
If Not MWS Is AWS Then
FAR = AWS.UsedRange.Cells(AWS.UsedRange.Cells.Count).Row + 1
LR = MWS.UsedRange.Cells(MWS.UsedRange.Cells.Count).Row
MWS.Range(MWS.Rows(NHR + 1), MWS.Rows(LR)).Copy AWS.Rows(FAR)
End If
Next MWS

End Sub

To install this macro, go to the VBE (keyboard Alt-TMM), insert a new macro module (Alt-IM), and paste the above code into the Code pane. Before running the macro select all the sheets you want to merge (Ctrl-click on tab). The active worksheet will be the one that was active before selecting the others.