Pivot Table Type Mismatch when More than 65536 rows

[RahmReza]

Hi,
I'm new in this discussion group and will appreciate if someone can help me
in finding a workaround with the problem having with creating pivottable in
excel 2007. I have the following code and get Type Mismatch error at line
"Set PivC...". This pivottable is based off 200,000 records in a worksheet
called "Data". I can maually create the pivottable without any error message.
I have found that the error message is generated when I use data records of
more than 65536. Upto 65536 records, code runs fine. Has anyone run in to
problem like this? Will really appreciate your help. Thanks.

Sub CreateSpendPivotTable()
'
' CreateSpendPivotTable Macro
'

Dim wB As Workbook
Dim PivC As PivotCache
Dim PivT As PivotTable

Application.DisplayAlerts = False
Worksheets("Data").Select

Set PivC = ActiveWorkbook.PivotCaches.Create _
(xlDatabase, Range("A1").CurrentRegion)
Sheets("%Spend Matching").Select
Set PivT = PivC.CreatePivotTable _
(TableDestination:=Worksheets("%Spend Matching").Range("B2"), _
TableName:="PivotTable1")

 

[Arthur Jenkins]

This is what you are currently doing:


Set PivC = ActiveWorkbook.PivotCaches.Create _
(xlDatabase, Range("A1").CurrentRegion)

For some reason the syntax Range("A1").CurrentRegion
will not work if there are more than 65,536 rows. You need to use R1C1 notation in place of this syntax to tell Excel the location of the data. See below.

You need to declare two variables:

Dim finalrow As Long
Dim finalcolumn As Long

Assign the number of the last row and last column of your data to the variables:

finalrow = Cells(Rows.Count, 1).End(xlUp).Row
finalcolumn = Cells(1, Columns.Count).End(xlToLeft).Column

Then create the pivotcache as follows:

Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, "R1C1:R" & finalrow & "C" & finalcolumn)

I then do the following to create a blank pivottable:

Worksheets.Add
pc.CreatePivottable ActiveCell
Set pt = ActiveCell.PivotTable

I hope this helps.

Regards