Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Type Mismatch when More than 65536 rows
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") |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2007 PivotTable Type Mismatch for records more than 65536 | Excel Programming | |||
Pivot Table type mismatch | Excel Programming | |||
Type Mismatch: array or user defined type expected | Excel Programming | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
Pivot Tables Formatting interior color &Type Mismatch | Excel Programming |