CODEWITHSUNDEEP
vbaexcelpivot-table
user1475657
user1475657

Reputation: 13

Pivot Table VBA Macro with Headers That Change

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

Upvotes: 1

Views: 8392

Answers (1)

SeanC
SeanC

Reputation: 15923

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

Upvotes: 1

Related Questions