Pivot Table VBA

[Eric58132]

Hi everyone,

I'm not sure if this one is doable but figured I would test the waters. I have an access table that is imported with new field names(the rolling last 15 months of data) and I'm bringing it from Access in to Excel. My goal was to pull this data straight into a pivot table with the 15 date fields (which are ALWAYS the last 15 fields in my access table) in the data portion of a pivot table.

The VBA to add the fields in this manner looks like this:

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Apr-09"), "Sum of Apr-09", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("May-09"), "Sum of May-09", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Jun-09"), "Sum of Jun-09", xlSum

Is there a way to make the Pivotfields("APR-09"/"May-09"/"Jun-09" be brought into the pivot table based upon their position in the pivot table field list, as opposed to their actual names? The month names will change as the file is used, but they will ALWAYS be the last 15 fields.

Any ideas? And thank you in advance.

[Bob Phillips]

[vba]

With ActiveSheet

lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = lastcol - 16 To lastcol

With .PivotTables("PivotTable1")

Heading = ActiveSheet.Cells(1, i).Text
.AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields(Heading), Heading & " ", xlSum
End With
Next i
End With
[/vba]