VBA runtime error 13 - Type mismatch for pivot

[Wafee]

Below is my code and I am getting type mismatch or object not defined error while running it. Error is at "Set Pcache" line. Code still creates a empty pivot table and throwing an error. Can someone help me with this. Any other suggestion to below version is always welcomed.

Worksheets("Pivot").Delete
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Pivot"
Set Psheet = Worksheets("Pivot")
Set Dsheet = Worksheets("Source Data")
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). _
CreatePivotTable(TableDestination:=Psheet.Cells(1, 1), _
TableName:="View 2")
Set PTable = PCache.CreatePivotTable _
(TableDestination:=Psheet.Cells(1, 1), TableName:="View 2")

 

[Yongle]

Try this - it's a lazy fix but hopefully will sort it out

On Error Resume Next
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=Psheet.Cells(1, 1), _
TableName:="View 2")
Set PTable = PCache.CreatePivotTable _
(TableDestination:=Psheet.Cells(1, 1), TableName:="View 2")
On Error GoTo 0

 

[RoryA]

You're trying to assign a pivotcache using a method that creates a pivot table. Also, try using the address instead of a range object, like this:

Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:="'" & DSheet.name & "'!" & PRange.address(referencestyle:=xlR1C1))

 

[Wafee]

You're trying to assign a pivotcache using a method that creates a pivot table. Also, try using the address instead of a range object, like this:

Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:="'" & DSheet.name & "'!" & PRange.address(referencestyle:=xlR1C1))

Thnaks mate, that worked.