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
Best 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.
How it works: