Copying a workbook sheet to the bottom of another workbook sheet

[Mr_Phil]

Hi Everyone. I pull multiple reports from the same source and they are all built (same number of columns & same headers) identically. Only the data is different. Obviously I can copy them, go to the very bottom of the first workbook data, and paste in the first row. That was fine where there were only five reports. Now there are sixteen and that might be going up. So I was wondering if there is a short macro I can put in my macro workbook that will automate that? I tried the macro recorder and it worked except that instead of the first blank row on the main sheet it went to the cell reference where I pasted it while recording it. That could be data lost but I can't quite figure out how to change the cell reference to the first blank row. I appreciate anyone looking and offering advice. If this is already asked/answered I'd be grateful for a link so I can learn a bit. Thanks again.

Sub Macro3()
'
' Macro3 Macro
'

'
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("OFFICE DATA TTWO.xlsx").Activate
'this is where I would like to tell it to select the first column A cell so that it will paste the data there.
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWindow.SmallScroll Down:=6
    Range("A157").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    Windows("OFFICE DATA TTT.xlsx").Activate
    ActiveWindow.Close
End Sub

Thank you again for looking and have a wonderful weekend

 

[Joe4]

Replace this:

    Range(Selection, Selection.End(xlDown)).Select
    ActiveWindow.SmallScroll Down:=6
    Range("A157").Select

with this:

    Cells(Rows.Count, "A").End(xlUp).Offset(1,0).Select

This will select the cell after the last populated row in column A.

 

[Mr_Phil]

Replace this:

    Range(Selection, Selection.End(xlDown)).Select
    ActiveWindow.SmallScroll Down:=6
    Range("A157").Select

with this:

    Cells(Rows.Count, "A").End(xlUp).Offset(1,0).Select

This will select the cell after the last populated row in column A.

That did the trick. Thank you very much.

 

[Joe4]

You are welcome.
Glad I was able to help.