Macro to Combine Data from all tabs

[stlpony]

Having issues with this macro ... It's copying the headings which is great, but I need it to automatically copy as much data is on each tab, and then move to the next tab. Any tips? Thanks!



Sub CombineAllTabs()
Dim J As Integer
Dim s As Worksheet


On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"


' copy headings
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1:A1")


For Each s In ActiveWorkbook.Sheets
If s.Name <> "Combined" Then
Application.GoTo Sheets(s.Name).[a1]
Selection.CurrentRegion.Select
' Don't copy the headings
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets("Combined"). _
Cells(Rows.Count, 1).End(xlUp)(2)
End If
Next
End Sub

 

[alansidman]

Here is a MrE video that should walk you through the steps. Fairly simple and easy to follow.

https://www.youtube.com/watch?v=LSDQGWdgNJs

 

[exce101]

Here is a MrE video that should walk you through the steps. Fairly simple and easy to follow.

https://www.youtube.com/watch?v=LSDQGWdgNJs

Unfortunately doesn't seem practical for what i am doing. I have numerous worksheets in numerous books. Having to convert them all to tables, etc and then go through a number of steps with power query only to lose all the formatting.

Unfortunately not fit for purpose for what i am trying to achieve.

Are you able to help with amending the macro for what i want to do?

 

[alansidman]

crossposted: https://www.excelforum.com/excel-pr...ta-ranges-from-multiple-tabs.html#post5177939

Please read: https://www.excelguru.ca/content.php?184

Crossposting without links is a form of abuse.