Copy/ Destination to different workbook. Trouble with the Syntax

[graym463]

Hi thanks for looking.

Please could you advise me on whats wrong with the setup of the line below? Or if i need to use a different method.

Its looping through workbooks and im trying to get it to use the last available row.

Thanks very much

Selection.Copy Desination:=Workbooks("AllAccounts").Sheet1.Range(Rows.Count, 1).End(xlUp).Offset(1, 0)

[berlan]

Hi,

I believe you can't use the sheet codename like Thisworkbook.Sheet1 or Workbooks("AllAccounts").Sheet1. Instead use e.g. Thisworkbook.sheets("Sheet1") where "Sheet1" is the sheet name.

Try,

Selection.Copy Workbooks("AllAccounts").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)(2)

and change the sheet name (in blue) as needed.

EDIT: changes needed, including the cell reference.

[Norie]

Well the first thing is the incorrect spelling of Destination .

Other than that it's hard to tell without knowing what errors you are getting.

[graym463]

Haha oh dear, I am going VBlind! Thank you both very much, that has corrected the issue!

final code snippet

    Do While Filename <> ""
'        If Not wb.Name = XLSFolder & "AllAccounts.xlsx" Then
        Workbooks.Open (XLSFolder & Filename)
    Set rngX = ActiveSheet.Range("A:A").Find("DATE", lookat:=xlPart) ' 1st DATE
    Set OfValue = rngX.Offset(1, 0) 'Finds date address 1 offset first value
        
        OfValue.Offset(1, 5).Select
        Range(Selection, Selection.End(xlToLeft)).Select
        Range(Selection, Selection.End(xlDown)).Offset(-1, 0).Select
        Selection.Copy Destination:=Workbooks("AllAccounts").Sheets("AllAccounts").Range("A" & Rows.Count).End(xlUp)(2)
        ActiveWorkbook.Close False
        Filename = Dir()
    Loop