带有更改标题的数据透视表 VBA 宏

Pivot Table VBA Macro with Headers That Change

提问by user1475657

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, 2012through November, 2012and renames them to Mayand 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?

我一直在研究一个宏来创建数据透视表。我让它按原样工作，但随着源数据的变化，我需要能够让宏继续工作。列标题根据当前月份而变化。A 列到 H 列始终相同，因此这些都不是问题，但 I 列到 O 列代表当前月份，然后是接下来的 6 个月。我需要宏来使用列 I 到 O 作为数据字段，然后适当地为它们命名。例如，我当前的宏May, 2012通过显示November, 2012并将它们重命名为May和Nov（我使用旧数据来制作这个宏）。有没有办法让宏创建带有 I 到 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

采纳答案by SeanC

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

这将从当前日期到 6 个月后运行：

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