Excel VBA - 概括枢轴源数据/范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17034430/
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 - Generalize Pivot Source Data / Range
提问by ggmkp
I recorded the pivot table macro and I'm trying to generalize source data instead of going off of sheet name "REPORTS"
我记录了数据透视表宏，我试图概括源数据，而不是脱离工作表名称“REPORTS”
It grabs all the data from active sheet despite what the name of the sheet.
不管工作表的名称是什么，它都会从活动工作表中获取所有数据。
This way I can use the macro to create a pivot table for any active sheet:-
这样我就可以使用宏为任何活动工作表创建数据透视表：-
Sheets("**REPORTS**").Select
Range("A1").Select
Sheets.Add.Name = "Pivot"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
Sheets("**REPORTS**").Range("A1").CurrentRegion, Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:="Pivot!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion15
Sheets("Pivot").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1")
.InGridDropZones = True
.RowAxisLayout xlTabularRow
End With
回答by Tim Williams
Sub TT()
Dim shtSrc As Worksheet, shtDest As Worksheet
Dim pc As PivotCache
Set shtSrc = ActiveSheet
Set shtDest = shtSrc.Parent.Sheets.Add()
shtDest.Name = shtSrc.Name & "-Pivot"
Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=shtSrc.Range("A1").CurrentRegion)
pc.CreatePivotTable TableDestination:=shtDest.Range("A3"), _
TableName:="PivotTable1"
With shtDest.PivotTables("PivotTable1")
.InGridDropZones = True
.RowAxisLayout xlTabularRow
End With
End Sub
回答by Ripster
This will not add any data to the pivot table but it will create it
这不会向数据透视表添加任何数据，但会创建它
Sub Example()
Dim PrevSheet As Worksheet
Set PrevSheet = ActiveSheet
Range("A1").Select
Sheets.Add.Name = "Pivot"
PrevSheet.Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=ActiveSheet.UsedRange, _
Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:="Pivot!R3C1", _
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion15
Sheets("Pivot").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1")
.InGridDropZones = True
.RowAxisLayout xlTabularRow
End With
End Sub
