VBA to auto rebuild of Pivot Table DataField collapse

[birosd]

I have been given the task to maintain a report with multiple pivot tables that summarize Data Source sheets that get updated weekly. The Data Sources have multiple column headers that change (e.g. Oct, Nov, Dec & 9/14, 9/21, 9/28). Thus, every time the pivot tables are refreshed, PivotFields get dropped and need to be re-added manually … to me a royal pain! Anyway, I have been trying to automate the “rebuild” of these pivot tables. I’m vba novice. I have code that will add a positioned PivotField (shown below), however the caption’s title has to be entered. I am looking for a way to pass the text in the data source column to the code that updates the pivot. I posted the version that works when typed in, and the version that does not work where I am trying to pass the text from the data source. I believe I am close, but I just cannot figure out why this approach is not working. Any help or ideas will be much appreciated.

Code that works:

Sub pivottable1()

   With ActiveSheet.PivotTables("PivotTable4").PivotFields("Sep Actual Hrs thru 9/25")
      .Orientation = xlDataField
      .Position = 2
   End With

End Sub

My attempt to retrieve the caption text from the data source (that does not currently work):

Sub PivotFieldAdd()

Dim pf As PivotField

Worksheets("Actual Data").Select   'name of the data source
'pf = Range("I3").Value

    'With ActiveSheet.PivotTables("PivotTable4").PivotFields("pf")    'this doesn't work either.
    With ActiveSheet.PivotTables("PivotTable4").PivotFields = Range("I3").Value
        .Orientation = xlDataField
        .Position = 2
   End With

End Sub

 

[Worf]

Like this:

Sub pivot_adjust()
Dim pt As PivotTable, pf$
Set pt = ActiveSheet.PivotTables("pivottable2")
pf = "Division"
'pf = ActiveSheet.[I3]           ' cell value
With pt.PivotFields(pf)
    .Orientation = 3
    .Position = 2
End With
End Sub

 

[birosd]

(SOLVED) Thank you Worf! Using your example, I change a few things in my code and now it works perfectly! So now when the source data column heading changes, the pivot table is updated.

Sub PivotFieldAdd()
Dim pf As String
pf = Worksheets("Actual Data").[I3] 'name of the data source
'Selection.Copy
'pf = Range("I3").Copy
'MsgBox Worksheets("Actual Data").Range("I3").Value
   With ActiveSheet.PivotTables("PivotTable4").PivotFields(pf)    'this doesn't work either.
   'With ActiveSheet.PivotTables("PivotTable4").PivotFields = Range("I3").Value
        .Orientation = xlDataField
        .Position = 2
   End With
End Sub

 

[Worf]

You are welcome!