Hello

I have a code which is giving me an error, every time I try to create a pivot cache. The same code (with very slight changes) is working for another data sheet.

I have not written the codes for inserting the pivot fields, since I am getting errors in this pivot cache for now. Please help.

This is the code I have:

Sub Comp_pvt()
Dim PSheet As Worksheet 'pivot sheet and data sheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range ' define the range of the pivot table
Dim LastRow As Long, LastCol As Long 'get the last row & column of our data range
'Dim myfolder As String, myfile As String, filename As String

Application.DisplayAlerts = False

'insert a new sheet for the pivot table and the data sheet

On Error Resume Next

Sheets("Compliance Pivots").Delete

On Error GoTo 0

Sheets.Add.Name = "Compliance Pivots"

Set PSheet = Sheets("Compliance Pivots") 'destination sheet for the pivot
Set DSheet = Sheets("Comp_dedup") ' source sheet for the pivot

LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange)


Set PTable = PCache.CreatePivotTable(tabledestination:=PSheet.Range("a2"), tablename:="Pivot")

End Sub