Adding Data Fields to Pivot Tables
 

My data source has months as headers with dollar amounts below. When I
add a new month to data source and delete the oldest month I want to
be able to add the new month to the pivot table. I think I can do this
by making the header a variable in VBA. Here is what I have:

Sub InsertNewMonth()

Dim Month14
Set Month14 = Worksheets("Detail").Range("Y3")

' I got this from the macro recorder
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Jun-09")
.Orientation = xlDataField
.Caption = "Sum of " & Month14
.Position = 14
.Function = xlSum
End With
ActiveSheet.PivotTables("PivotTable1").DataPivotFi eld.PivotItems( _
"Sum of Jun-09").Position = 14

End Sub

I want to be able to take the variable Month14 and insert it into the
pivot table in position 14 as a sum.

Thanks for your help.