PivotCache "Type mismatch" error when >65,536 rows

Question

Using Excel 2010.

A pivot table process that works when there are <= 65,536 rows in the

SourceData range fails as soon as the row count goes >65,536. Debugger

returns a "Type Mismatch" error at the Set PTCache = portion of the

script highlighted below. Included are two methods I've tried for the

SourceData, neither works.

Is there a way to make this work for more rows?

Below is the section of script with two approaches I've tried. Both work when <65,537 and both fail when >. Thanks -- KR

Public Sub SalesContactReport()

Dim pt As PivotTable

Dim strField As String

Dim WSD As Worksheet

Dim PTOutput As Worksheet

Dim PTCache As PivotCache

Dim PRange As Range

Dim pi As PivotItem

Dim finalRow As Long

Dim finalCol As Long

'Set pivot table worksheet as active

Sheets("Table").Activate

' Initialize worksheets

Set WSD = ActiveWorkbook.Worksheets("Detail")

Set PTOutput = ActiveWorkbook.Worksheets("Table")

*******************************************

This was my 1st approach

*******************************************

' Find the last row with data

finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row

' Find the last column with data

finalCol = WSD.Cells(1,

Application.Columns.Count).End(xlToLeft).Column

' Find the range of the data

Set PRange = WSD.Cells(1, 1).Resize(finalRow, finalCol)

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,

SourceData:=PRange) << Type mismatch error.

*******************************************

This was my 2nd approach

*******************************************

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,

SourceData:= _

Worksheets("Detail").Range("A1").CurrentRegion) << Type mismatch error

.

.

.

.

.

End Sub

Answer 1

If you run the 2010 macro recorder while recording the creation of a pivot table from your long range you will find it generates different code, including a Version parameter speecifying xlPivotTableVersion14.

I think your code is generating an Excel 2003-type pivot table that would have had a 65536 row limitation on the source range.

Answer 2

If you run the 2010 macro recorder while recording the creation of a pivot table from your long range you will find it generates different code, including a Version parameter speecifying xlPivotTableVersion14.

I think your code is generating an Excel 2003-type pivot table that would have had a 65536 row limitation on the source range.

Excellent thanks! Running the recorder got this thing back on track. I'd run it earlier and didn't catch the difference in the SourceData:= section. My code is using a Range reference while the recorder uses the R1C1 reference. Using a Range, even with the v14 entries, still caused a Type Mismatch. However switching to R1C1 gets it working.

So, this example fails with a Type Mismatch error:

    Set PRange = WSD.Cells(1, 1).Resize(finalRow, finalCol)

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

        PRange, Version:=xlPivotTableVersion14).CreatePivotTable _

        TableDestination:="Table!R1C1", TableName:="PivotTable2", DefaultVersion _

        :=xlPivotTableVersion14

This example works!:

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

        "Detail!R1C1:R" & finalRow & "C" & finalCol).CreatePivotTable _

        TableDestination:="Table!R1C1", TableName:="PivotTable2"

Thank you for the help. It forced a closer look at the code differences and uncovered the problem.