Pivot Table Type Mismatch when More than 65536 rows
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
|