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

[drummr8]

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

 

[Domenic]

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...

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

Hope this helps!