PDA

View Full Version : Trying to copy all sheets into one combined sheet



dazz
08-24-2017, 05:17 PM
Hi, In my workbook, I have many sheets with different amount of columns and different amount of rows. I just want a 'combined' sheet with the contents of the first sheet followed by the contents of the second sheet, followed by the contents of the third sheet..ect...

I am using the following which is giving me the sheets appended in order, but its not copying all the columns :/
Appreciate any assistance with this. Im also open to a different solution you may be willing to share


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

jolivanes
08-24-2017, 08:32 PM
Please use code tags around your code.


Sub Maybe()
Dim sh As Worksheet
If Not [ISREF(Combined!A1)] Then
Sheets.Add(, Sheets(Sheets.Count)).Name = "Combined"
Else
Sheets("Combined").UsedRange.EntireColumn.Delete
End If
Sheets("Sheet2").Rows(1).Copy Sheets("Combined").Cells(1, 1)
For Each sh In ActiveWorkbook.Sheets
If sh.Name <> "Combined" Then sh.UsedRange.Offset(1).Copy Sheets("Combined").Cells(Rows.Count, 1).End(xlUp).Offset(1)
Next sh
End Sub