I am creating a crosstab Excel worksheet from a query in Access. I would then
like to copy ranges of cells from the crosstab Excel worksheet into another
worksheet using Excel automation, but I am having a problem with the
automation commands to do this. Could you please tell me what I am doing
wrong or point me to an example that I could reference. The code that I have
so far is below. Thanks

Private Sub cmdPatientStatus_Click()
On Error GoTo Err_cmdPatientStatus_Click
   
   Dim xl As Excel.Application
   Dim xlBook1 As Excel.Workbook
   Dim xlBook2 As Excel.Workbook
   Dim xlsheet1 As Excel.Worksheet
   Dim xlsheet2 As Excel.Worksheet
   Dim sheet1path As String
   Dim sheet2path As String
   
   'Export crosstab query to Excel worksheet
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryPatientStatus_Crosstab1", "N:\Studies\Screening\PatientStatus_temp.xls",
True
   
   sheet1path = "N:\Studies\Screening\PatientStatus_temp.xls"
   sheet2path = "N:\Studies\Screening\qryPatientStatus_CrosstabTemplate.xls"
   
   Set xl = CreateObject("Excel.Application")
   Set xlBook1 = GetObject(sheet1path)
   Set xlBook2 = GetObject(sheet2path)
   
   xlBook1.Windows(1).Visible = True
   xlBook2.Windows(2).Visible = True
   
   Set xlsheet1 = xlBook1.Worksheets(1)
   Set xlsheet2 = xlBook2.Worksheets(2)
   
   'Copy range of cells in Worksheet1
   xlsheet1.Activate
   xlsheet1.Range("a1:b100").Select
   xlsheet1.Copy
   
   'Paste range of cells from Worksheet1 into worksheet2
   xlsheet2.Activate
   xlsheet2.Range("a1").PasteSpecial
   
   'Close all workbooks and worksheets
   xlBook1.Close
   xl.ActiveWorkbook.SaveAs "N:\Studies\Screening\PatientStatus_New.xls"
   xlBook2.Close
   xl.Quit
   Set xl = Nothing
   Set xlBook1 = Nothing
   Set xlBook2 = Nothing
   
Exit_cmdPatientStatus_Click:
   Exit Sub

Err_cmdPatientStatus_Click:
   MsgBox Err.Description
   Resume Exit_cmdPatientStatus_Click
   
End Sub