VBA Query for skipping space and header from multiple sheet while consolidating data (from multiple sheets into 1 sheet of same excel)

[syprakash]

I am using below code to extract & consolidate data from multiple sheets into 1 new sheet of same excel file.
But this Macro copying all rows from all sheets. I want to extract only active data (want to stop extracting when there is no data (space/empty) in Column 'A', and move into next sheet (or tab) for extraction.

Also i want to skip copying header row(1st row) from each sheet (tab).

Could you please share me code for the same.

Sub Combine()
    Dim I As Long
    Dim xRg As Range
    On Error Resume Next
    Worksheets.Add Sheets(1)
    ActiveSheet.Name = "Combined"
   For I = 2 To Sheets.Count
        Set xRg = Sheets(1).UsedRange
        If I > 2 Then
            Set xRg = Sheets(1).Cells(xRg.Rows.Count + 1, 1)
        End If
        Sheets(I).Activate
        ActiveSheet.UsedRange.Copy xRg
    Next
End Sub

 

[JackDanIce]

Try:

Sub Combine()
    
    Dim w   As Long
    Dim x   As Long
    Dim y   As Long
    
    Application.ScreenUpdating = False
    
    y = sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column
    Worksheets.Add sheets(1)
    With ActiveSheet
        .Name = "Combined"
        .Cells(1, 1).Resize(, y).Value = sheets(2).Cells(1, 1).Resize(, y).Value
    End With
    
    For w = 2 To sheets.Count
        With sheets(w)
            y = .Cells.Find(What:="*", after:=.Cells(1, 1), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            x = .Cells(.Rows.Count, 1).End(xlUp).Row - 1
            sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(x, y).Value = .Cells(2, 1).Resize(x, y).Value
        End With
    Next w
    
    Application.ScreenUpdating = True
    
End Sub

 

[syprakash]

Still it copies entire data from each sheet, I want to stop extracting in particular tab when there is no data (space/empty) in Column 'A'(even some data left out after empty row), and move into next sheet (or tab) for extraction.