Jan 15 2020 04:23 AM
Hello,
I was wondering if anyone can help?
I have 8 identical worksheets (numbered 2-8)in a workbook that I need to create an identical summary from. My columns are A - S.
I do have a code but I have a couple of questions:
How do I get the header (In A1 in all sheets) to appear only once on my summary page?
Is there a way that I can delete the previous summary each time a new one is created?
My code is:
Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A2").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A2")
For J = 2 To 8 ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A2").Select
Selection.CurrentRegion.Select ' select all cells in this sheets
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub
Many thanks
Jan 15 2020 06:54 AM
Almost perfect. Just a small change in the rows above the "For J ........."
Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To 8 ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A2").Select
Selection.CurrentRegion.Select ' select all cells in this sheets
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub