Combine Work Sheets

[sduttexcel]

I have 10 work sheets in excel. All the work sheets has the same same fields. I want to combine all the work sheets in once spread sheet. In some work sheet in between rows are empty. If it sees empty rows it stops there. The rows after blank are not combines. Finding hard to modify the macro. Also I want to avoid header records from work sheet 2 and onwards.
many thanks!

Sub CombineSheets()


Dim ws As Worksheet, wsCombine As Worksheet
Dim rg As Range
Dim RowCombine As Integer


Set wsCombine = ThisWorkbook.Worksheets.Add(ThisWorkbook.Worksheets(1))
wsCombine.Name = "Combine"


RowCombine = 1
For Each ws In ThisWorkbook.Worksheets
If ws.Index <> 1 Then
Set rg = ws.Cells(1, 1).CurrentRegion
rg.Copy wsCombine.Cells(RowCombine, 2)
wsCombine.Range(Cells(RowCombine, 1), Cells(RowCombine + rg.Rows.Count - 1, 1)) = ws.Name
RowCombine = RowCombine + rg.Rows.Count
End If
Next
wsCombine.Cells(1, 1).EntireColumn.AutoFit
Set rg = Nothing
Set wsCombine = Nothing

End Sub

 

[Fluff]

How about

         Set rg = ws.UsedRange.Offset(1)

 

[sduttexcel]

It leaves a blank field in place of blank rows

 

[Fluff]

Could you please explain what you mean?

 

[sduttexcel]

Do anybody has another idea. Much appreciated.