Copy and Paste between Workbooks

[beginvbaanalyst]

Hi Everyone,

Please help.
I'm wanting to open a closed workbook with a macro, then make the variable, then set the variable, then copy from a separate (already open) workbook and paste into the new set workbook. How do I do this correctly?
I keep getting this error.

My current vba is the following:

Sub OpenAddClubsScript()

Workbooks.Open Filename:="A:\Scripts Library\WSM3_Listing_Template_9.17.18.xlsx"
Dim ListScript As Workbook

'Prompt Sheet1[LIST]
'Workbooks("ListScript").Activate
Set ListScript = Sheets("LIST") 'set sheet for formula

'With LISTARTICLES.Range("A2", LISTARTICLES.Range("A" & Rows.Count).End(xlUp)).Copy LIST.Range("A" & Rows.Count).End(xlUp).Offset(1)



End Sub

 

[AntMac]

Just from looking with the naked eye you have listscript as a workbook object, but you are saying it's the worksheet. I would suggest using

Set wb = ThisWorkbook

Then get the sheet you want using wb.worksheet("List")

 

[GWteB]

Not sure if this is what you mean, it might help you a bit.

Sub OpenAddClubsScript()

    ' make some variables
    Dim LISTARTICLES    As Worksheet    ' source
    Dim oShtDest        As Worksheet    ' destination

    ' set some variables
    Set LISTARTICLES = ThisWorkbook.Sheets("LIST")
    Set oShtDest = Workbooks.Open(Filename:="A:\Scripts Library\WSM3_Listing_Template_9.17.18.xlsx").ActiveSheet

    ' perform copy from source sheet to destination sheet
    LISTARTICLES.Range("A2", LISTARTICLES.Range("A" & Rows.Count).End(xlUp)).Copy oShtDest.Range("A" & Rows.Count).End(xlUp).Offset(1)

End Sub

 

[beginvbaanalyst]

Not sure if this is what you mean, it might help you a bit.

Sub OpenAddClubsScript()

    ' make some variables
    Dim LISTARTICLES    As Worksheet    ' source
    Dim oShtDest        As Worksheet    ' destination

    ' set some variables
    Set LISTARTICLES = ThisWorkbook.Sheets("LIST")
    Set oShtDest = Workbooks.Open(Filename:="A:\Scripts Library\WSM3_Listing_Template_9.17.18.xlsx").ActiveSheet

    ' perform copy from source sheet to destination sheet
    LISTARTICLES.Range("A2", LISTARTICLES.Range("A" & Rows.Count).End(xlUp)).Copy oShtDest.Range("A" & Rows.Count).End(xlUp).Offset(1)

End Sub

I think this is EXACTLY what I'm looking for.
I will try in the morning and let you know if this works.
Fingers crossed