Macro Combine Error?

  • Thread starter Thread starter JR
  • Start date Start date
J

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
 
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
 
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
 
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
 
Back
Top