Copying Excel worksheets from one workbook to another

[DC CS]

Hi all,
I am trying to copy a worksheet in one Excel workbook, to another Excel workbook. There is only 1 sheet in the source workbook that needs to be copied.

I found some examples in other forums, but I receive runtime error 9 - Subscript out of range when I run the code.

Code below:

Dim CopyFromBook As WorkbookDim CopyToBook As Workbook
Dim ShToCopy As Worksheet


Set CopyToBook = Workbooks("c:\temp\WF_Conversion_08-Mar-2023.XLSX")
For Each ShToCopy In Workbooks("c:\temp\WF_Conversion_BackPay_08-Mar-2023.XLSX").Worksheets
    ShToCopy.Copy After:=CopyToBook.Sheets(CopyToBook.Sheets.Count)
Next ShToCopy

The error occurs in the "SET CopyToBook...." statement.
Any suggestions in what I am doing wrong?
Thanks

[June7]

Where are you running this code?

[DC CS]

I am running the code from within a module.
I am calling the sub from the main form.

[June7]

So you are running in Access?

Sub CopySht()
Dim xl As Excel.Application
Dim wbMaster As Excel.Workbook
Set xl = New Excel.Application
Set wbMaster = xl.Workbooks.Open("c:\temp\WF_Conversion_BackPay_08-Mar-2023.XLSX")
With xl.Workbooks.Open("c:\temp\WF_Conversion_08-Mar-2023.XLSX")
    wbMaster.worksheets(1).Copy After:=.worksheets(.worksheets.Count)
    .Close True
End With
wbMaster.Close
xl.Quit
End Sub

Based on code in https://stackoverflow.com/questions/...s-in-ms-access

[DC CS]

Thanks June7 - resolved!!