Copy all sheets into a new one

[Yulyo]

Hello all,

I have a workbook with around 500 sheets. All sheets look the same, same number of columns, etc.
I need a vba to copy the content from all sheets into a new one and paste them one after another.

Here is what i managed to do until now...

Sub Combine()

Worksheets.Add
Sheets(1).Name = "Combined"



Sheets(1).Range("A1").EntireRow.Value = Sheets(2).Range("A1").EntireRow.Value 'not most effecient, but will do for this



Dim J As Integer
For J = 2 To Sheets.Count ' from sheet 2 to last sheet
    With Sheets(J)
        .Range(.Cells(2, 1), .Cells(.Range("A" & .Rows.Count).End(xlUp).Row, .Cells(2, .Columns.Count).End(xlToLeft).Column)).Copy _
             Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(2)
    End With
Next


End Sub

The problem that i have right now, is that if the Sheet "Combined" already exists, i recieve runtime error 1004 (the name is already taken).

Regarding the fact that i will execute this code a few times everyday, this is a dealbreaker...

Could you please help me with a solution? How can i delete the sheet "Combined" befor executing the rest of the code?

Thank you in advance.

 

[Fluff]

How about

Sub Combinesheets()
   Dim Ws As Worksheet
   Dim ComWs As Worksheet
   
   If Not Evaluate("isref(Combined!A1)") Then Sheets.Add(1).Name = "Combined"
   Set ComWs = Sheets("Combined")
   ComWs.UsedRange.Offset(1).Clear
   For Each Ws In Worksheets
      If Not Ws.Name = "Combined" Then
         Ws.UsedRange.Offset(1).Copy ComWs.Range("A" & Rows.Count).End(xlUp).Offset(1)
      End If
   Next Ws
End Sub

 

[Yulyo]

Thank a lot, Fluff

Could you please help me making the vba to copy only the rows that contains data? (right now i have a few hundred rows between sheets...)

Thank you

 

[Fluff]

Do you have formulae in the sheets extending below the data?

 

[Yulyo]

Every sheet have some empty rows bellow the data, in case some new data must be entered.
So, I think that the empty rows are imported too, into "combined" sheet...

 

[Fluff]

Is there any data below the "Empty Rows"?

 

[Yulyo]

No

 

[Fluff]

In that case I suspect that the "Empty" rows are not empty. They may have spaces, hidden characters, or Formula that return "".