用于具有动态数据范围的数据透视表的 Excel VBA 宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12586507/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA Macro for Pivot Table with Dynamic Data Range
提问by Liquidgenius
CODE IS WORKING! THANKS FOR THE HELP!
代码正在运行！谢谢您的帮助！
I am attempting to create a dynamic pivot table that will work on data that varies in the number of rows. Currently, I have 28,300 rows, but this may change daily.
我正在尝试创建一个动态数据透视表，它可以处理行数不同的数据。目前，我有 28,300 行，但这可能每天都在变化。
Example of data format as follows:
数据格式示例如下：
Case Number Branch Driver
1342 NYC Bob
4532 PHL Jim
7391 CIN John
8251 SAN John
7211 SAN Mary
9121 CLE John
7424 CIN John
Example of finished table:
成品表示例：
Driver NYC PHL CIN SAN CLE
Bob 1 0 0 0 0
Jim 0 1 0 0 0
John 0 0 2 1 1
Mary 0 0 0 1 0
Code as follows:
代码如下：
Sub CreateSummaryReportUsingPivot()
' Use a Pivot Table to create a static summary report
' with model going down the rows and regions across
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Dim FinalCol As Long
Set WSD = Worksheets("PivotTable")
'Name active worksheet as "PivotTable"
ActiveSheet.Name = "PivotTable"
' Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange)
' Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
Cells(2, FinalCol + 2), TableName:="PivotTable1")
' Turn off updating while building the table
PT.ManualUpdate = True
' Set up the row fields
PT.AddFields RowFields:="Driver", ColumnFields:="Branch"
' Set up the data fields
With PT.PivotFields("Case Number")
.Orientation = xlDataField
.Function = xlCount
.Position = 1
End With
With PT
.ColumnGrand = False
.RowGrand = False
.NullString = "0"
End With
' Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
End Sub
回答by scott
They changed the object model for PivotCaches. The method you need in 2007-2010 (that use VBA version 7 instead of version 6) is
他们更改了 PivotCaches 的对象模型。您在 2007-2010 中需要的方法（使用 VBA 版本 7 而不是版本 6）是
PivotCaches.Create
回答by pedram
Any reason you are using VBA other than the changing number of rows?
除了改变行数之外，您使用 VBA 的任何原因？
If you are using Excel 2007 / 2010, create a regular table /list (Ctrl-L) from your original data. You can give it a name as well. Then create a Pivot Table and use the table name as the data source. As you add rows your table will expand and you can then just refresh the Pivot Table (F5, or using VBA).
如果您使用的是 Excel 2007 / 2010，请根据您的原始数据创建一个常规表 /list (Ctrl-L)。你也可以给它一个名字。然后创建一个数据透视表并使用表名作为数据源。当您添加行时，您的表格将扩展，然后您可以刷新数据透视表（F5，或使用 VBA）。
If you are in Excel 2003, you can create dynamic named ranges as well. It's slightly more complex (and a lot more ugly) but I can walk you through it if you are stuck on an older version.
如果您使用的是 Excel 2003，您也可以创建动态命名区域。它稍微复杂一些（也更丑陋），但如果您坚持使用旧版本，我可以引导您完成它。
