VBA Summary Page

[ClaireW1871]

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

[#2]

@ClaireW1871 

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