使用 VBA 创建数据透视表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20607776/
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
Creating a Pivot Table using VBA
提问by Bramat
I wrote this VBA code for creating a Pivot Table (converted it from a Macro, actually).
我写了这个 VBA 代码来创建一个数据透视表（实际上是从宏转换过来的）。
Dim wsTarget As Worksheet
Dim rngSource As Range
Dim pc As PivotCache
Dim pt As PivotTable
Dim field As PivotField
Application.ScreenUpdating = False
Set rngSource = Summary.Range("A1").CurrentRegion
Set wsTarget = PivotTable
wsTarget.Select
For Each pt In wsTarget.PivotTables
pt.Delete
Next pt
Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, rngSource, xlPivotTableVersion15)
Set pt = pc.CreatePivotTable(wsTarget.Range("A3"), "PivotTable1", , xlPivotTableVersion15)
Set field = wsTarget.PivotTables("PivotTable1").PivotFields("A")
field.Orientation = xlColumnField
field.Position = 1
field.LayoutBlankLine = True
Set field = wsTarget.PivotTables("PivotTable1").PivotFields("B")
field.Orientation = xlRowField
field.Position = 1
Set field = wsTarget.PivotTables("PivotTable1").PivotFields("TOTAL")
Set field = wsTarget.PivotTables("PivotTable1").AddDataField(field, "Sum of TOTAL", xlSum)
field.NumberFormat = "_ $ * #,##0.00_ "
Application.ScreenUpdating = True
The code crashes at the Set pcline.
代码在该Set pc行崩溃。
I tried googling, and all of the results were identical to my code.
我尝试使用谷歌搜索，所有结果都与我的代码相同。
I'm working with Excel 2013.
我正在使用 Excel 2013。
回答by Sathish Kothandam
try this..i think you are directly referring the sheet name..
试试这个..我认为你是直接引用工作表名称..
Set rngSource = Sheets("Summary").Range("A1").CurrentRegion
Set wsTarget = sheets("PivotTable")
回答by Brett Rykwalder
Try this:
尝试这个：
Dim lrow As Long
Dim lcol As Long
Dim rngSource As Range
lrow = ActiveSheet.Cells(Application.Rows.Count, 1).End(xlUp).Row
lcol = ActiveSheet.Cells(1, Application.Columns.Count).End(xlToLeft).Column
Set rngSource = ActiveSheet.Cells(1, 1).Resize(lrow, lcol)
回答by srboisvert
Way late but did you have a header row?
迟到了，但你有标题行吗？
https://msdn.microsoft.com/en-us/library/office/Ff196678.aspx
https://msdn.microsoft.com/en-us/library/office/Ff196678.aspx
