Using Excel Automation I am copying a range of values from worksheet1 to
worksheet2. I know the copy is working because when I print out the values
from worksheet2 the values that I copied from worksheet1 print in the
immediate window as expected. The problem that I am having is that when I
save worksheet2 and then open it in Windows explorer the values that I copied
either don't appear or the workbook has no worksheet at all (the workbook is
blank), both of these senerios have occured. Not sure why this is happening.
Any suggestions would be appreciated, I have been trying to get this to work
for awhile. Here is my code:

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 r1 As Range
   Dim r2 As Range
   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)
   Set xlsheet1 = xlBook1.Worksheets("qryPatientStatus_Crosstab1")
   Set xlsheet2 = xlBook2.Worksheets("qryPatientStatus_Template")
   
   xlBook1.Activate
   xlBook2.Activate
   xlsheet1.Activate
   xlsheet2.Activate
   Debug.Print xlsheet1.Range("A1").Value
   Debug.Print xlsheet2.Range("G1").Value
     
   'Copy range of cells in Worksheet1 to Worksheet2
   Set r1 = xlsheet1.Range("A1:A5")
   Set r2 = xlsheet2.Range("A1:A5")
   r1.Copy r2
   xlBook2.Save
   
   'Close all workbooks and worksheets
   Set xlsheet1 = Nothing
   Set xlsheet2 = Nothing
   Set xlBook1 = Nothing
   Set xlBook2 = Nothing
   xl.Quit
   Set xl = Nothing
   
Exit_cmdPatientStatus_Click:
   Exit Sub

Err_cmdPatientStatus_Click:
   MsgBox Err.Description
   Resume Exit_cmdPatientStatus_Click
   
End Sub