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")
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,534
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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))
 
Solution

Wafee

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

Watch MrExcel Video

Forum statistics

Threads
1,128,119
Messages
5,628,799
Members
416,340
Latest member
PJB1102

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
Top