VBA script can't combine my excel sheets

Hi,

Just a newbie here, not really experienced in coding or such, instead I pulled a script from the internet to serve one purpose.

Which is to combine all the sheets in my excel file onto one single sheet where all the data will be collated. It works for some file, but at the same time, it doesn't for some, due to empty spaces in between, may I know if there is a solution to this?

Attached is my test file, any help is appreciated.

Sub Combine()
'UpdatebyExtendoffice
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

Quote from royUK

You need to remove all those rows with only numbers in column A, otherwise they will be copied

Code

Sub Combine()
    
    Dim oWs As Worksheet, TargetWS
    Dim rRng As Range
    Dim iX As Integer
    
    Set TargetWS = Worksheets.Add
    TargetWS.Name = "Combined"
    
    For Each oWs In ThisWorkbook.Worksheets
        Select Case oWs.Name
            Case "Script", "Combined"
                'Do nothing
            Case Else
                iX = iX + 1
                ''/// copy headers first time
                If iX = 1 Then
                    oWs.Range("A7").CurrentRegion.Copy TargetWS.Range("A1")
                Else
                    Set rRng = oWs.Range("A7").CurrentRegion
                    Set rRng = rRng.Offset(1, 0).Resize(rRng.Rows.Count - 1, _
                    rRng.Columns.Count)
                    With TargetWS
                        rRng.Copy .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
                    End With
                    End If
            End Select
    Next oWs
End Sub

Display More

You are a blessing, this worked out so well after deleting column A. Thank you so much!

Quote from royUK

You need to remove all those rows with only numbers in column A, otherwise they will be copied

Code

Sub Combine()
    
    Dim oWs As Worksheet, TargetWS
    Dim rRng As Range
    Dim iX As Integer
    
    Set TargetWS = Worksheets.Add
    TargetWS.Name = "Combined"
    
    For Each oWs In ThisWorkbook.Worksheets
        Select Case oWs.Name
            Case "Script", "Combined"
                'Do nothing
            Case Else
                iX = iX + 1
                ''/// copy headers first time
                If iX = 1 Then
                    oWs.Range("A7").CurrentRegion.Copy TargetWS.Range("A1")
                Else
                    Set rRng = oWs.Range("A7").CurrentRegion
                    Set rRng = rRng.Offset(1, 0).Resize(rRng.Rows.Count - 1, _
                    rRng.Columns.Count)
                    With TargetWS
                        rRng.Copy .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
                    End With
                    End If
            End Select
    Next oWs
End Sub

Display More

As for the column A, is there any way to in-corporate the following code into your vba code? Such that every time I do not have to delete column A in 10+ sheets. Once again, thank you very much for your assistance

Sub sbVBS_To_Delete_EntireColumn ()
    Columns(1).EntireColumn.Delete
End Sub

Quote from CedricYong

As for the column A, is there any way to in-corporate the following code into your vba code? Such that every time I do not have to delete column A in 10+ sheets. Once again, thank you very much for your assistance

Code

Sub sbVBS_To_Delete_EntireColumn ()
    Columns(1).EntireColumn.Delete
End Sub

Actually you know what, I looked through the training on the website, and I got it all figured out. Thank you very much, just found this website out today, and it is really useful for people like me. I would love to learn VBA to do stuff like these myself.