Combine Multiple Worksheets Into One

Carlito · January 16, 2019

MSExcel

I haven’t tested it for some time, but this should work for merging two Excel worksheets, in one spreadsheet, into a third one - Combined.

  1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
  2. Click Insert > Module, and paste the following code in the Module Window
Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub
  1. Then press F5 key to run the code, and all the data in the workbook has been merged into a new worksheet named Combined which will add before all worksheets.

Resources

Twitter, Facebook