Error 1004 VBA copy Excel 4.0 worksheet to Excel 2010

Question

I've written some VBA code hosted in Access 2010 to clean up a Excel 4.0 workbook that contains unwanted AutoRun macros - as I was only interested in the data, it seemed a good idea to copy the specific worksheet to a new Excel 2010 workbook instead.

However, when the VBA gets to the .Copy statement, I get error:

1004 Method 'Copy' of object '_Worksheet' failed.

This isn't the MS bug with multiple copies, or not saving before copying, etc. This is trying to copy into an existing workbook. Whilst this error code seems popular here, I can't find an approach and/or answer that works for me.

The problematic part of the code:

'declarations
Dim varFile As Variant
Dim varNewFile As Variant
Dim xlApp As Excel.Application
Dim xlWbk As Excel.Workbook
Dim xlWb2 As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim xlSh2 As Object 'will be a member of the Sheets collection (not worksheets)

'filenames - constPath is a constant defining the pathname to the folder
varFile = constPath & "\site.xls"
varNewFile = constPath & "\sitenew.xlsx"

'create instance of Excel, open source workbook and point to worksheet
Set xlApp = CreateObject("Excel.Application")
Set xlWbk = GetObject(varFile)
Set xlSht = xlWbk.Worksheets("sites")

'open destination workbook (already exists)
Set xlWb2 = GetObject(varNewFile)
    
    'rename worksheet with same name
    For Each xlSh2 In xlWb2.Sheets
        If xlSh2.Name = "sites" Then
            xlSh2.Name = "sitesOLD"
        End If
    Next xlSh2
    
'this next statement is where it fails
    'copy across worksheet into existing workbook
    xlSht.Copy Before:=xlWb2.Sheets(1)

I've examined the source data and there doesn't appear to be issues with the cell values in terms of length (it's name and address information, plus a few Yes/No indicators).

Answer 1

There could be numerous reasons for this. Does putting xlApp.xlSht.Copy work?