Hi,

Can someone pls check below code.
I'm able to export a query from access to excel in file "Myfile.xls"
sheet1 but unable to make a sheet2 with pivot table of sheet1 data.

Option Compare Database
Public Sub TransferReport()

Dim varFileName As String
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim MyRange As String

varFileName = "D:\MyFile.xls"
'EXPORT DATA
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MONTH
END REPORT", varFileName, False, "Sheet1"

Set xlWb = ActiveWorkbook
xlWb.Sheets.Add
Set xlWs = xlWb.Sheets("Sheet2")


xlWs.Cells(2, 1).CopyFromRecordset rsXcl

xlWs.Range("A1").Addresslastcell = xlW.Range("A1").SpecialCells
(xlCellTypeLastCell).Address
MyRange = Range("$A$1:" & lastcell)

' Add pivot table
With xlWb
.PivotCaches.Add SourceType:=xlDatabase, _
SourceData:=MyRange
.CreatePivotTable TableDestination:="", _
tablename:="Pivottable1"
End With

End Sub