VBA runtime error 13 - Type mismatch for pivot

Wafee

Board Regular
Joined
May 27, 2020
Messages
104
Office Version
  1. 2013
Platform
  1. Windows
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")
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Try this - it's a lazy fix but hopefully will sort it out
Rich (BB code):
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
 
Upvote 0
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:

Code:
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:="'" & DSheet.name & "'!" & PRange.address(referencestyle:=xlR1C1))
 
Upvote 0
Solution
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:

Code:
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:="'" & DSheet.name & "'!" & PRange.address(referencestyle:=xlR1C1))
Thnaks mate, that worked.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top