VBA Creation of Pivot Cache, Pivot Table - Error 13 Type Mismatch

drummr8

New Member
Joined
Apr 25, 2017
Messages
6
I am trying to use VBA to automatically create a pivot table, and I am getting error 13. I have highlighted the specific line where it is occurring. Please help!

Relevant info:
My column headers start in row 9 of my data sheet
There are ~60,000 lines of data with columns out to AJ

Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long


Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("RWK Report")


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


Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange).CreatePivotTable(TableDestination:=PSheet.Range("B2"), TableName:="InheritedPivotTable")


Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(5, 1), TableName:="InheritedPivotTable")

Thanks
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
It looks like for some versions of Excel the PivotCaches.Create method may cause a type mismatch error if you have more than 65,536 rows of data. So try running your code with a smaller size of data to determine whether in fact it is the cause of the error.

Also, try using a string to specify the range for the SourceData argument. Apparently, assing a Range object may also cause a type mismatch error, as per Excel's help file...

The SourceData argument is required if SourceType isn't xlExternal . It should be passed a Range (when SourceType is either xlConsolidation or xlDatabase ) or an Excel Workbook Connection object (when SourceType is xlExternal ). When passing a Range, it is recommended to either use a string to specify the workbook, worksheet, and cell range, or set up a named range and pass the name as a string. Passing a Range object may cause "type mismatch" errors unexpectedly.

By the way, I don't think that you're defining the range for your source data correctly. You'll end up with blank rows at the end of the defined range. Try the following instead...

Code:
With DSheet
    Set PRange = .Range(.Cells(9, 1), .Cells(LastRow, LastCol))
End With

Hope this helps!
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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