Macro Combine Error?

[JR]

Hello,
If someone can tell me where the error is in this macro, I would greatly
appreciate it. What I am in need for this macro to accomplish, is to combine
all sheets to one master sheet. For some reason I am testing it on three
sheets and the output is not correct?

Thanks JR

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"

' copy headings
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")

' work through sheets
For J = 1 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A1").Select
Selection.CurrentRegion.Select ' select all cells in this sheets

' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

 

[JR]

Sorry,
Forgot to mention. For some reason when it makes the master list, the first
row is blank, it erases the first row headers. Also if possible, can it over
write the master list sheet if macro has to run more than once.

Thanks JR

 

[Dave Unger]

Hi

I think your basic problem is the following line:

For J = 1 To Sheets.Count ' from sheet 2 to last sheet

Change the J=1 to J=2, eg.

For J = 2 To Sheets.Count ' from sheet 2 to last sheet

I'll have a "think" about your other question.

DaveU

 

[Dave Unger]

Regarding your 2nd question, try changing your code to the following:

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("Combined").Select
If Err <> 0 Then
Sheets.Add before:=Worksheets(1): Sheets(1).Name = "Combined"
Else
Cells.Clear
End If

Hope this works for you,

DaveU

 

[JR]

Thanks so much, works perfect. Great news group, nothing but friendly help.

Thanks JR