vba - Excel - Copy data from multiple sheets to one sheet

I use a program that exports data to multiple sheets and I need these all in the same sheet under one another.

About the worksheets - They vary in names every export, so this needs not be defined, as there are sometimes 100+ different sheets.

Inside the worksheets - Is data of varying rows and columns (and I need it to copy until blank row into the main sheet at the next available row that is blank).

The worksheet that all the data needs to be copied too - This will initially be blank, however I need all the data on top of one another based on a row copy from the other worksheets. (this can have a defined name if required)

I'm new to VBA, so hopefully explanation above helps.

My current VBA not working:

 Sub SheetLoopPasteData()


Dim ws As Worksheet
Dim wsSheet As Worksheet
Set wsSheet = Sheets("Sheet2")

For Each ws In Worksheets
    ws.Activate
    variable = Cells(Rows.Count, 1).End(xlUp).Row
    Rows("1:" & variable).Copy _
    Destination:=wsSheet.Range("A" & (wsSheet.Range("A" & wsSheet.Rows.Count).End(xlDown).Row + 1))


Next



End Sub

Answer

I would like to suggest VBA (Macro) will help you to Copy used range from all Worksheets into New, exactly works as Data Consolidation.

Sub CombineData()


    Dim I As Long
    Dim xRg As Range

    On Error Resume Next

    Worksheets.Add Sheets(1)

    ActiveSheet.Name = "MasterSheet"


   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

How it works:

• Copy & Paste this code as Module.


• Macro will create new sheet in existing Workbook named as MasterSheet, which is editable.


• In place of copy data from Top Row to last, I've used the UsedRange method, since will accommodate every updates.