How to paste data into next available blank row

[anne.gomes]

Hi, I'm having trouble with pasting my coped data from one workbook into a next available blank row in the other workbook. This is what I have so far:

Sub Copy()
Application.DisplayAlerts = wdAlertsNone
dt = Format(CStr(Now), "mmmm d, yyyy")
    ChDir "C:\Users\anneg\Desktop"
    Workbooks.Open Filename:="C:\Users\anneg\Desktop\Book1.xlsx"
    Rows("1:1").Select
    Selection.Copy
    ActiveWindow.Close
    
    Range("A1").Select 'how to select next empty row on the worksheet and paste it onto that
    ActiveSheet.Paste
    Application.CutCopyMode = False


End Sub

Any help would be great,

Thank you in advance

[Paul_Hossler]

This could be made much more general since there are assumptions I made regarding the destination worksheet data layout

Sub test()
    MsgBox LastRow.Address
    LastRow.Select
End Sub


'assumes activesheet, and that there is at least one row with data starting in row 1
Function LastRow() As Range
    Dim iRow As Long
    iRow = ActiveSheet.Cells(1, 1).CurrentRegion.Rows.Count + 1
    Set LastRow = ActiveSheet.Rows(iRow)
End Function

Post a small sample of the real destination sheet if you want

[anne.gomes]

Here is a sample of the destination spreadsheet:

ID	ORGANIZATION_ELEMENT_ID	ORGANIZATION_ELEMENT_CODE	ORGANIZATION_ELEMENT_NAME	COMPANY_ID	COMPANY_CODE	COMPANY_NAME	INVOICE_NUMBER	SUPPLIER_ID
fd4caba20b5d4c2daf767abde287679d	FES	FES	Easy Steel	FES	FES	Easy Steel	SI861816	8bbc65aa263b474fb2f575cc4ac7a9b9
58a3d41a46db4f95b5bd868660d59c7c	FES	FES	Easy Steel	FES	FES	Easy Steel	24246	920935c01f4b4e5d8608866521d7807a
13f5f8268ba24dfdbc2451dbe01d630e	FES	FES	Easy Steel	FES	FES	Easy Steel	24254	920935c01f4b4e5d8608866521d7807a
9356fb7d4a34421da805a76efbd12028	FES	FES	Easy Steel	FES	FES	Easy Steel	1289264	71927b29df7644e09bfd34d70741d147
389eefbb768849d5befa5eedde735a72	FES	FES	Easy Steel	FES	FES	Easy Steel	1289276	71927b29df7644e09bfd34d70741d147

I want the macro to go to the last row with data and paste the the newly copied data into the next empty row.

[EirikDaude]

I'd say replacing ActiveSheet.Paste with LastRow.Paste would do what you want, assuming you copy Paul Hossler's function into your VBA code. Unless there is some trouble with the data you copy not staying on the clipboard after you close the workbook you are copying from?

I notice that you turn off alerts in your sub, presumably to not get the "Do you want to save"-dialog. You can circumvent this by adding the argument "false" to the function call (i.e. activeworkbook.close(false)) if you don't want to save changes, or with the argument "true" if you do want to save them. If you decide to stick with your current solution, you probably want to turn the alerts back on at the end of the sub?