Combine data on various sheets into one seperate sheet

[MartinSpellacy]

Afternoon,

I have tried recording the below macro which looks to combine the data on 9 tabs into one tab. Problem I have is that the data on each of the 9 tabs is dynamic and may vary in number of rows.

I believe I have identified the problem and have made the code bold and underlined.

Any help as to how this can work would be appreciated

Thanks
Martin

Sheets("Ab").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Combine").Select
    Range("A2").Select
    ActiveSheet.Paste
    Sheets("Bh").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Combine").Select
    Range("A2").Select
    Selection.End(xlDown).Select
[U][B]    Range("A237").Select[/B][/U]
[U][B]    ActiveSheet.Paste[/B][/U]
    ActiveWindow.SmallScroll Down:=6
    Sheets("Dm").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Combine").Select
    Range("A205").Select
    Selection.End(xlDown).Select
[U][B]    Range("A782").Select[/B][/U]
[U][B]    ActiveSheet.Paste[/B][/U]
    Sheets("Dd").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Combine").Select
    Range("A738").Select
    Selection.End(xlDown).Select
    Range("A995").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=3
    Sheets("Gg").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Combine").Select
    Range("A936").Select
    Selection.End(xlDown).Select
    Range("A1097").Select
    ActiveSheet.Paste
    Sheets("Gm").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Combine").Select
    Range("A1061").Select
    Selection.End(xlDown).Select
    Range("A1302").Select
    ActiveSheet.Paste
    Sheets("Inv").Select
    Range("A11").Select
    Selection.End(xlUp).Select
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Combine").Select
    Range("A1272").Select
    Selection.End(xlDown).Select
    Range("A1774").Select
    ActiveSheet.Paste
    Sheets("Nb").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Combine").Select
    Range("A1715").Select
    Selection.End(xlDown).Select
    Range("A2246").Select
    ActiveSheet.Paste
    Sheets("VMU").Select
    Range("A18").Select
    ActiveWindow.SmallScroll Down:=-12
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 2
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Combine").Select
    Range("A2208").Select
    Selection.End(xlDown).Select
    Range("A2718").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=3

 

[MartinSpellacy]

any help would be so appreciated

 

[vcoolio]

Hello Martin,

Assuming that your data has headings in row1 and data starts in row2, try the following code:-

Sub Consolidate()

Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In Worksheets
If ws.Name <> "Sheet1" Then   '---->Change "Sheet1" to the actual name of your main sheet.
       ws.UsedRange.Offset(1).Copy
       Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
       End If
Next ws

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.