MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Merging worksheets

Posted by SLP on January 25, 2002 12:17 PM


I have two worksheets that I would like to combine into one. I can then (hopefully) sort the combined cells so that everything containing the same part # will be together.

Cut and paste or drag and drop will be too time intensive.

Any suggestions?

Posted by Joe C on January 25, 2002 2:23 PM

Use the Vlookup.
If the files are so big that it takes a long time. I often use the V lookup on one column, which is the value of the row with the data. I then write a really quick macro to bring that rows information into the other spreadsheet.

Posted by Damon Ostrander on January 25, 2002 3:03 PM


Here's a little macro that makes merging sheets easy. Just select the sheets you want merged making sure the active sheet (visible one) is the one you want to merge to. The rows from all the other selected sheets will be copied to end of the active sheet in left-to-right order of worksheet tabs. It allows you to declare a number of header lines (NHR value) so that you don't copy the worksheet's headers to the first sheet.

This macro should be put into a macro module. If you don't know how to install/run a macro, just follow up and I'll give you instructions.


Sub MergeSheets()

' Merges 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
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