copy a sheet to a close workbook

[lezawang]

Hi
Can I copy a sheet from ThisWorkbook to another workbook which is closed but in the same folder. Is that possible or the second workbook has to be open. I know how to move/copy a sheet inside same workbook or to another opened workbook but my code below failed to copy a sheet to second workbook which is closed. Thank you so much.

Sub ws_copy_out()
    Dim x As Workbook
    Set x = Workbooks("book2.xlsx")
    Workbooks(1).Worksheets(1).Copy after:=x.Worksheets(1)
End Sub

 

[sadboy309]

If Workbooks Book2.xlsx exist, so you can open it in the same folder. After, you can copy Sheet to those Workbook Book2. Code Open ThisWorkbook:

Sub ws_copy_out()    
    Dim x As Workbook
    Set x = Workbooks.Open(ThisWorkbook.Path & "\" & "book2.xlsx")
    Workbooks(1).Worksheets(1).Copy after:=x.Worksheets(1)
End Sub

 

[lezawang]

Thank you for your help. I thought I can do it without "opening" the second workbook. But since you suggested to open then why I need to "set x=", can I just open the workbook without assigning it to a workbook variable, like the following:

Sub ws_copy_out()
    Dim x As Workbook
    Workbooks.Open Filename:="book2.xlsx"
    Workbooks(1).Worksheets(1).Copy after:=Workbooks(2).Worksheets(1)
End Sub

What is the difference between your code and mine? Thank you.