PivotCache "Type Mismatch" error with large data set

[SeanDamnit]

Hello Internet,

I have a macro that combines a few reports, does some calculation on the data, and sums everything up in a pivot table. The process works great when my combined raw data has ~36000 line items. However if I expand my reporting range and end up north of 60000 line items, I get a "Type Mismatch" error when attempting to set the PivotCache. See code snippet below:

Sub AgedInvReport()
Dim newBook As Workbook
Dim wbData As Worksheet
Dim wbSum As Worksheet
Dim PRange As Range
Dim PTCache As PivotCache
Dim pt1 As PivotTable
Dim pt2 As PivotTable
Dim LRData As Long


'SNIP SNIP'


'===========Setup Pivot Table==========='
Workbooks("Skynet.xla").Sheets("Aged Inventory").Copy Before:=wbData
Set wbSum = newBook.Sheets("Aged Inventory")

LRData = wbData.Cells(wbData.Rows.Count, colInvSku).End(xlUp).Row
Set PRange = wbData.Cells(1, 1).Resize(LRData, 19)


With wbSum
    .Range(.Columns(14), .Columns.End(xlToRight)).EntireColumn.Hidden = True
    Set PTCache = .Parent.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange, Version:=xlPivotTableVersion12) 'getting the error here
    Set pt1 = PTCache.CreatePivotTable(TableDestination:=.Cells(11, 2), TableName:="ByLocation", DefaultVersion:=xlPivotTableVersion12)
    Set pt2 = PTCache.CreatePivotTable(TableDestination:=.Cells(11, 7), TableName:="BySKU", DefaultVersion:=xlPivotTableVersion12)


'SNIP SNIP'

Why would this work just fine with 36k lines, but not with 60k lines? Pretty sure I'm properly declaring my variables here...any advice?

 

[Jerry Sullivan]

Hi Sean,

The limit occurs when trying to pass a Range object with greater than 65536 rows (max number of rows for xl2003), even if you are using xl2007 or xl2010.

The SourceData argument, which is a Variant type, handles either Range objects or Strings representing Range Address references below this limit.

For Ranges greater than 65536 rows, it appears to only work with Address References and not Range Objects.

A workaround for your code would be to convert the Range object into an Address reference.

SourceData:=PRange.Address(1, 1, xlA1, External:=True)

 

[SeanDamnit]

Hi Sean,

The limit occurs when trying to pass a Range object with greater than 65536 rows (max number of rows for xl2003), even if you are using xl2007 or xl2010.

The SourceData argument, which is a Variant type, handles either Range objects or Strings representing Range Address references below this limit.

For Ranges greater than 65536 rows, it appears to only work with Address References and not Range Objects.

A workaround for your code would be to convert the Range object into an Address reference.

SourceData:=PRange.Address(1, 1, xlA1, External:=True)

Worked great. Now I just need a way for this not to take 15 minutes to run.

Thanks!

 

[Jerry Sullivan]

Is the code in your snippet taking that long- or is the bottleneck elsewhere in the macro?