Calculated fields in pivot to extend sourcetable of 180 columns

[ebotman]

Hi!

I have a sheet with about 180 columns showing for each month data as budget sales, last year sales, forecast sales, actual sales, price variances, volume variances, etc.

I have written a macro to create a pivottable linked to this source and would like to include calculated fields in this macro to accumulate monthly data in order to incorporate columns with Year-to-date, Rest-of-year, and Full Year data. For example a simplified version of the pivottable source (sheet2) looks like ( I don´t know how to attach spreadsheets to this post):

A1: Region
A2: North
A3: West

B1 to M1: Sales M01, Sales M02, Sales M03, Sales M04 etc

The macro is assuming YTD (Year to Date) is January - March, and looks as follows:

Sub create_pivot1()

Dim PTCache As PivotCache
Dim PT As PivotTable
Dim Lastrow, Lastcolumn As Long

Application.ScreenUpdating = True

'Delete any prior pivottables
For Each PT In Sheet1.PivotTables
PT.TableRange2.Clear
Next

Sheet2.Select
Lastrow = Cells(60000, 1).End(xlUp).Row
Lastcolumn = Cells(1, 256).End(xlToLeft).Column
Set PRange = Range(Cells(1, 1), Cells(Lastrow, Lastcolumn))
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange.Address)

'Create the pivottable
Set PT = PTCache.CreatePivotTable(TableDestination:=Sheet1.Range("A1"), TableName:="Pivottable1")

'Set up the row & column fields
PT.AddFields RowFields:=Array("Region"), ColumnFields:="Data"
   
'Define field names.
field1 = "Sales M01"
field2 = "Sales M02"
field3 = "Sales M03"
field4 = "Sales M04"
field5 = "Sales M05"
field6 = "Sales M06"
field7 = "Sales M07"
field8 = "Sales M08"
field9 = "Sales M09"
field10 = "Sales M10"
field11 = "Sales M11"
field12 = "Sales M12"

cfield1 = "Sales YTD"
cfield2 = "Sales FY"
cfield3 = "Sales ROY"

Cfield1_Formula = "=('" & field1 & "')+('" & field2 & "')+('" & field3 & "')"
PT.CalculatedFields.Add cfield1, Cfield1_Formula

Cfield2_Formula = "=('" & field1 & "')+('" & field2 & "')+('" & field3 & "')+('" & field4 & "')+('" & field5 & "')+('" & field6 & "')+('" & field7 & "')+('" & field8 & "')+('" & field9 & "')+('" & field10 & "')+('" & field11 & "')+('" & field12 & "')"
PT.CalculatedFields.Add cfield2, Cfield2_Formula

Cfield3_Formula = "=('" & cfield2 & "')-('" & cfield1 & "')"
PT.CalculatedFields.Add cfield3, Cfield3_Formula

'Set up the datafields
With PT.PivotFields(field1)
        .Orientation = xlDataField
        .Function = xlSum
        .Caption = " " & field1
        .NumberFormat = "#,##0_);[Red](#,##0)"
        .Position = 1
End With

With PT.PivotFields(field2)
        .Orientation = xlDataField
        .Function = xlSum
        .Caption = " " & field2
        .NumberFormat = "#,##0_);[Red](#,##0)"
        .Position = 2
End With

With PT.PivotFields(field3)
        .Orientation = xlDataField
        .Function = xlSum
        .Caption = " " & field3
        .NumberFormat = "#,##0_);[Red](#,##0)"
        .Position = 3
End With

With PT.PivotFields(cfield1)
        .Orientation = xlDataField
        .Function = xlSum
        .Caption = " " & cfield1
        .NumberFormat = "#,##0_);[Red](#,##0)"
        .Position = 4
End With

With PT.PivotFields(cfield2)
        .Orientation = xlDataField
        .Function = xlSum
        .Caption = " " & cfield2
        .NumberFormat = "#,##0_);[Red](#,##0)"
        .Position = 5
End With

With PT.PivotFields(cfield3)
        .Orientation = xlDataField
        .Function = xlSum
        .Caption = " " & cfield3
        .NumberFormat = "#,##0_);[Red](#,##0)"
        .Position = 6
End With

'deselect grand totals
With PT
.ColumnGrand = False
.RowGrand = False
End With

'Calc the pivottable
PT.ManualUpdate = False
PT.ManualUpdate = True
End Sub

However, as said the real sourcesheet contains 180 columns, which would make the VBA scipt incredibly long if I would one-by-one set up each pivotfield as displayed in the VBA script above. On top of that, each month I would have to adjust the formula for the calculated field "YTD" (cfield1).

Summarized I am looking for the following solutions:
1) How can I avoid setting up the pivot fields one-by-one? Eg. is it possible to include a loop?
2) How can I make the calculated field cfield1 (YTD) formula dynamic, i.e. changing when the month changes.
3) Is it possible to shorten the formula for calculated field cfield2 (Full Year) ?

I know I am asking a lot, but I thought let's try though I will continue trying to find a solution among the thousands of great posts and replies on this forum. In fact I would be surprised if no-one else in a finance reporting environment has faced this issue before.

Many thanks
Emiel