From Access VBA Copy an Excel Sheet to a different Excel Workbook, Values Only

I have a button on a form that runs a query with a date on the form being used as criteria in the query.  I also have an Excel Woorkbook containing a sheet that is linked to the query, and a pivot table taken from the linked sheet.

What I am trying to accomplish is this:

Open the Excel file from the form so the query has access to the date.  (I have accomplished this)
Copy the two sheets from the "Main" file to a newly named Excel file.  I only want the VALUES from the linked sheet as I don't need to keep the link.  The copied sheet with the pivot table should have no problem referring to the "Values only" spreadsheet.

I have learned how to copy a sheet to it's own workbook, but not to another workbook.  I have reasearched and found some valuable stuff on EE, but my lack of knowledge in Excel has left me wanting.  I am attaching the code to show you how far I have gotten.

The error I get is "Method 'Copy' of object '_Worksheet' failed" at the last line of the attached snippet.

So, FIRST, I have not acomplished copying the worsheet at all, and SECOND, I have not accomplished copying the "Values Only" of the spreadsheet.  I realize they will probably be the same step, but I was learning as I went, and could go no further.

Thanks . . HubbsStart Free Trial

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:

Dim xltmp As New Excel.Application
Dim wks1 As Excel.Worksheet
Dim wks2 As Excel.Worksheet
Dim wbkSource As Excel.Workbook
 
'Dim xlObj As Object
Dim xlApp As Excel.Application
Dim wbkNew As Excel.Workbook
Dim wbkTarget As Excel.Workbook
Dim wks3 As Excel.Worksheet
 
txtNameEnd = Format(Now(), "mmddyy_hh_nn_ss")
 
'Set xlObj = CreateObject("excel.application")
Set xlApp = New Excel.Application
xlApp.Visible = False
Set wbkNew = xlApp.Workbooks.Add
xlApp.ActiveWorkbook.SaveAs "P:\Reporting\ActivityReports\VCC_ActivityRpt_XL_" & Forms!frmReports.txtNameEnd & ".xls"
Set wbkTarget = xlApp.Workbooks.Open("P:\Reporting\ActivityReports\VCC_ActivityRpt_XL_" & Forms!frmReports.txtNameEnd & ".xls")
'xlApp.Visible = True
 
Set wbkSource = xltmp.Workbooks.Open("P:\Reporting\ActivityReports\Template\VCC_Activity_Report_Main.xls")
'xltmp.Visible = True
 
Set wks1 = wbkSource.Sheets("Sheet1")
Set wks2 = wbkSource.Sheets("Summary")
'Set wks3 = wbkTarget.Sheets("Sheet1")
 
wks1.Copy Before:=wbkTarget.Sheets("Sheet1")