Maximum Number of Fields in Pivot Table?? -WEIRD!

[jswhip]

I have a confusing VBA problem/bug that I don't know how to work around.

1. I define a pivot cache with 70 columns (this works fine)
2. However, I need 71 columns or else the calculated field I need won't show up in the field list.
3. But, Excel 2007 won't let me add a 71st column.
4. I've tried using dynamic/static ranges for the cache, but neither solved the problem.

I use the original source data to create multiple pivot tables on separate sheets - would this be the reason why I can't add another calculate field? The reason I ask is that the original source only has 36 columns, but in the course of adding calculated fields, I've had to expand the cache.

Dim WSD As Worksheet
Dim pc As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Dim FinalCol As Long

Set WSD = Worksheets("Raw Data")

FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol + 1)

' Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
' "Raw Data!R1C1:R100000C70", Version:=xlPivotTableVersion12)


Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="'" & WSD.Name & "'!" & PRange.Address(, , xlR1C1))

Set PT = pc.CreatePivotTable(TableDestination:=Sheets("Pivot Table 9").Range("A1"), TableName:= "PivotTable1", DefaultVersion:=xlPivotTableVersion12)

With PT

.CalculatedFields.Add "ROLL_3_MO_AUM%", "=IF('ROLL_3_MO_AUM' >0,'ROLL_3_MO_AUM+')/'ROLL_3_MO_AUM',"""")", True


.
.
.

 

[GlennUK]

That calculated field definition has unbalanced brackets. Wouldn't that cause a problem?

 

[jswhip]

Thanks.

I noticed that after numerous iterations. It's odd that it didn't flag a syntax error when I tried to execute the code.

NICE CATCH!

 

[GlennUK]

Great!