Pivot cache using vba - Type mismatch error

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

This is where the code stops executing:

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

How many rows of data do you have?

Originally Posted by rorya

How many rows of data do you have?

The data has to be dynamic since it can change on a month on month basis.

If you have a lot (more than 65536 rows) don't use a range object for the source, use an address string.

Originally Posted by rorya

If you have a lot (more than 65536 rows) don't use a range object for the source, use an address string.

The data would be in 100s only. It wouldwould never go up to this much.

I saw another code online, which was using the address. (,,,true) keyword. I used that and was able to pass the error issues. But what does that do?

It does what you need and is the option I always use. However you should not have this issue with only a few hundred rows. I suspect your range is larger than you think - what's the value of lastRow?

there are only 69 rows in the data sheet