2007 PivotTable Type Mismatch for records more than 65536
 

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")