Pivot Table VBA Macro with Headers That Change

Question

I have been working on a macro to create a pivot table. I have it working as is, but as the source data changes, I need to be able to have the macro continue to work. The column headings change based on the current month. Columns A through H are always the same, so those aren't a problem, but columns I through O represent the current month and then the following 6 months. I need the macro to use columns I through O as datafields and then title them appropriately. For example, my current macro displays May, 2012 through November, 2012 and renames them to May and Nov (I'm using old data to make this macro). Is there a way to have the macro create datafields with whatever headings are in columns I through O?

Sub CreatePivotTable()

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "CP Monthly Data!R1C1:R451C15", Version:=xlPivotTableVersion14).CreatePivotTable _
    TableDestination:="", TableName:="PivotTable1", DefaultVersion _
    :=xlPivotTableVersion14
ActiveSheet.PivotTables("PivotTable1").Name = "Resource Requests"
With ActiveSheet.PivotTables("Resource Requests")
    .InGridDropZones = True
    .AllowMultipleFilters = True
    .RowAxisLayout xlTabularRow
End With
With ActiveSheet.PivotTables("Resource Requests").PivotFields("Workgroup Name")
    .PivotItems("ATG").Visible = False
    .PivotItems("India - ATG").Visible = False
    .PivotItems("India - Managed Middleware").Visible = False
End With
With ActiveSheet.PivotTables("Resource Requests").PivotFields("Workgroup Name")
    .Orientation = xlPageField
    .Position = 1
End With
With ActiveSheet.PivotTables("Resource Requests").PivotFields("Company name")
    .Orientation = xlRowField
    .Position = 1
End With
With ActiveSheet.PivotTables("Resource Requests").PivotFields( _
    "Probability Status")
    .PivotItems("X - Lost - 0%").Visible = False
    .PivotItems("X - On Hold - 0%").Visible = False
End With
With ActiveSheet.PivotTables("Resource Requests").PivotFields( _
    "Probability Status")
    .Orientation = xlRowField
    .Position = 2
End With
With ActiveSheet.PivotTables("Resource Requests").PivotFields("Project")
    .Orientation = xlRowField
    .Position = 3
End With
With ActiveSheet.PivotTables("Resource Requests").PivotFields("Project manager" _
    )
    .Orientation = xlRowField
    .Position = 4
End With
ActiveSheet.PivotTables("Resource Requests").PivotFields("Resource name"). _
    PivotFilters.Add Type:=xlCaptionBeginsWith, Value1:="*TBD"
With ActiveSheet.PivotTables("Resource Requests").PivotFields("Resource name")
    .Orientation = xlRowField
    .Position = 5
End With
ActiveSheet.PivotTables("Resource Requests").TableStyle2 = "PivotStyleMedium4"

ActiveSheet.PivotTables("Resource Requests").AddDataField ActiveSheet. _
    PivotTables("Resource Requests").PivotFields("May, 2012"), "May" _
    , xlSum
ActiveSheet.PivotTables("Resource Requests").AddDataField ActiveSheet. _
    PivotTables("Resource Requests").PivotFields("June, 2012"), _
    "June", xlSum
ActiveSheet.PivotTables("Resource Requests").AddDataField ActiveSheet. _
    PivotTables("Resource Requests").PivotFields("July, 2012"), _
    "July", xlSum
ActiveSheet.PivotTables("Resource Requests").AddDataField ActiveSheet. _
    PivotTables("Resource Requests").PivotFields("August, 2012"), _
    "August", xlSum
ActiveSheet.PivotTables("Resource Requests").AddDataField ActiveSheet. _
    PivotTables("Resource Requests").PivotFields("September, 2012"), _
    "September", xlSum
ActiveSheet.PivotTables("Resource Requests").AddDataField ActiveSheet. _
    PivotTables("Resource Requests").PivotFields("October, 2012"), _
    "October", xlSum
ActiveSheet.PivotTables("Resource Requests").AddDataField ActiveSheet. _
    PivotTables("Resource Requests").PivotFields("November, 2012"), _
    "November", xlSum
Range("B6").Select
ActiveSheet.PivotTables("Resource Requests").PivotFields("Probability Status"). _
    Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
    False, False)
Range("C7").Select
ActiveSheet.PivotTables("Resource Requests").PivotFields("Project").Subtotals _
    = Array(False, False, False, False, False, False, False, False, False, False, False, False _
    )
Range("D7").Select
ActiveSheet.PivotTables("Resource Requests").PivotFields("Project manager"). _
    Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
    False, False)
Range("A18").Select
ActiveSheet.PivotTables("Resource Requests").PivotFields("Company name"). _
    Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
    False, False)
ActiveSheet.PivotTables("Resource Requests").PivotFields("Probability Status"). _
    AutoSort xlDescending, "Probability Status"
ActiveSheet.PivotTables("Resource Requests").PivotFields("Resource name"). _
    AutoSort xlAscending, "Resource name"

End Sub

One solution is to have a separate table built upon which your pivot table is based from. This table is update at each interval based on your source data. So, you can set the headings of the table to be whatever you want, but just link your source data to the columns / rows, using VBA or Excel formulas, or whatever is convenient to you. For example, in the Pivot Table data source, Column I is Month1, Column J is Month2, Column K is Month3 and so on, but the data is fed from your source data to whatever Month1, Month2, Month3 are. This way, your data is up to date, but headings never change. — Scott Holtzman
– Scott Holtzman, Commented Jun 25, 2012 at 16:46

SeanC · Accepted Answer · 2012-06-25 15:17:48Z

1

this will run though from current date to 6 months after:

Dim DVal As Date
Dim DLoop As Integer

DVal = Now() ' or whatever date you want to start at

For DLoop = 0 To 6 '0=now, to 6 months after
ActiveSheet.PivotTables("Resource Requests").AddDataField
    ActiveSheet.PivotTables("Resource Requests").PivotFields (Format(DateAdd("m", DLoop, DVal), "mmm, yyyy")), _
    Format(DateAdd("m", DLoop, DVal), "mmm"), _
    xlSum
Next DLoop

answered Jun 25, 2012 at 15:17

SeanC

16k5 gold badges48 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1475657 Over a year ago

Thank you so much for your reply! I tried your code and it throws an error: "Unable to get the PivotFields property of the PivotTable class"

SeanC Over a year ago

for the AddDataField, I copied the example code you had for your code. I haven't programatically added data to a pivot table, I just assumed your code was correct

Scott Holtzman Over a year ago

This answer does not address the users problem, it only formats the fields of the current pivot table. The user's issue is that as time goes on and the field headings change from May 2012 to June 2012 to July 2012, the pivot table won't refresh because it in June it can longer find May 2012 field heading and so on (this is a simplified version, but it makes the point).

user1475657 Over a year ago

Actually, I did some research and altered your code, Sean, and I think yours would work if you changed the first Format(DateAdd("m", DLoop, DVal), "mmm, yyyy")) to Format(DateAdd("m", DLoop, DVal), "mmmm, yyyy")) because my source data has the full month name. I ended up using the idea behind your code (just without the For loop). Thank you so much! I now have the code just like I need it!

Collectives™ on Stack Overflow

Pivot Table VBA Macro with Headers That Change

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related