我一直在研究一个宏来创建数据透视表。我让它按原样工作,但随着源数据的变化,我需要能够让宏继续工作。列标题根据当前月份而变化。列 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
这将从当前日期运行到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