‘ 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)
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.