Adding Data Fields to Pivot Tables

S

Shane

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").DataPivotField.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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Pivot Table Error 1
syntax in VBA to create a pivot table 2
Remover ODBC Dialog Box 0
remove add pivot table fields 0
Looking to remove pivot fields from pivot table via macro 0
Format Pivot total rows 2
remove or add items from a pivot tables 1
Playing back a Pivot Table macros 2

Top