Combining Excel Sheets into One
Combining Excel Sheets into One
PREWORK
All sheets must be in the same format. Basically, this just copies and pastes each sheet into a combined sheet.
No need to create a combined sheet. This macro will do that for you.
This will only copy up to 65K rows per sheet. This is the limit of older versions of Excel.
- Enter Microsoft Visual Basic for Applications mode by pressing ALT + F11
- Create a new module by clicking on Insert then Module
- Copy the following code then press F5
Sub Combine()
Dim I 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 I = 2 To Sheets.Count
Sheets(I).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