Summary VBA

[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

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub clairew()
   Dim Ws As Worksheet
   
   Sheets.Add(Sheets(1)).Name = "Combined"
   With Sheets("Combined")
      Sheets(1).Rows(1).Copy .Range("A1")
      For Each Ws In Worksheets
         If Ws.Name <> "Combined" Then
            Ws.UsedRange.Offset(1).Copy .Range("A" & Rows.Count).End(xlUp).Offset(1)
         End If
      Next Ws
   End With
End Sub

 

[Spyros13]

It skips a whole lot of data where column(a) is empty by columns (b),(c), .. etc contain data. what can i do to fix that? apart from inserting a new column(a) in all my sheets contaning row(id's) or some other data for each row ?

 

[Fluff]

Please start a thread of your own, giving full details. Thanks