Excel的Pivot Table在我们平时的办公场景 学习 以及 工作中都常用到,但如果想自动化快速操作透视表,那肯定离不开VBA,但如何通过VBA自动操作Pivot Table呢。下面列出国外一个作者有关VBA操作透视表Pivot Table代码大全,非常齐全
vba创建透视表:Create A Pivot Table
Sub CreatePivotTable()
'PURPOSE: Creates a brand new Pivot table on a new worksheet from data in the ActiveSheet
'Source: www.TheSpreadsheetGuru.com
Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String
'Determine the data range you want to pivot
SrcData = ActiveSheet.Name & "!" & Range("A1:R100").Address(ReferenceStyle:=xlR1C1)
'Create a new worksheet
Set sht = Sheets.Add
'Where do you want Pivot Table to start?
StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)
'Create Pivot Cache from Source Data
Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SrcData)
'Create Pivot table from Pivot Cache
Set pvt = pvtCache.CreatePivotTable( _
TableDestination:=StartPvt, _
TableName:="PivotTable1")
End Sub
vba
删除指定的透视表:Delete A Specific Pivot Table
Sub DeletePivotTable()
'PURPOSE: How to delete a specifc Pivot Table
'SOURCE: www.TheSpreadsheetGuru.com
'Delete Pivot Table By Name
ActiveSheet.PivotTables("PivotTable1").TableRange2.Clear
End Sub
VBA删除所有透视表:Delete All Pivot Tables
Sub DeleteAllPivotTables()
'PURPOSE: Delete all Pivot Tables in your Workbook
'SOURCE: www.TheSpreadsheetGuru.com
Dim sht As Worksheet
Dim pvt As PivotTable
'Loop Through Each Pivot Table In Currently Viewed Workbook
For Each sht In ActiveWorkbook.Worksheets
For Each pvt In sht.PivotTables
pvt.TableRange2.Clear
Next pvt
Next sht
End Sub
VBA添加透视表字段:Add Pivot Fields
Sub Adding_PivotFields()
'PURPOSE: Show how to add various Pivot Fields to Pivot Table
'SOURCE: www.TheSpreadsheetGuru.com
'translate by tmtony (www.office-cn.net)
Dim pvt As PivotTable
Set pvt = ActiveSheet.PivotTables("PivotTable1")
'Add item to the Report Filter
pvt.PivotFields("Year").Orientation = xlPageField
'Add item to the Column Labels
pvt.PivotFields("Month").Orientation = xlColumnField
'Add item to the Row Labels
pvt.PivotFields("Account").Orientation = xlRowField
'Position Item in list
pvt.PivotFields("Year").Position = 1
'Format Pivot Field
pvt.PivotFields("Year").NumberFormat = "#,##0"
'Turn on Automatic updates/calculations --like screenupdating to speed up code
pvt.ManualUpdate = False
End Sub
VBA添加透视表计算字段 :Add Calculated Pivot Fields
Sub AddCalculatedField()
'PURPOSE: Add a calculated field to a pivot table
'SOURCE: www.TheSpreadsheetGuru.com
Dim pvt As PivotTable
Dim pf As PivotField
'Set Variable to Desired Pivot Table
Set pvt = ActiveSheet.PivotTables("PivotTable1")
'Set Variable Equal to Desired Calculated Pivot Field
For Each pf In pvt.PivotFields
If pf.SourceName = "Inflation" Then Exit For
Next
'Add Calculated Field to Pivot Table
pvt.AddDataField pf
End Sub
VBA添加值字段:Add A Values Field
Sub AddValuesField()
'PURPOSE: Add A Values Field to a Pivot Table
'SOURCE: www.TheSpreadsheetGuru.com
'translate by tmtony
Dim pvt As PivotTable
Dim pf As String
Dim pf_Name As String
pf = "Salaries"
pf_Name = "Sum of Salaries"
Set pvt = ActiveSheet.PivotTables("PivotTable1")
pvt.AddDataField pvt.PivotFields("Salaries"), pf_Name, xlSum
End Sub
VBA删除透视表字段:Remove Pivot Fields
Sub RemovePivotField()
'PURPOSE: Remove a field from a Pivot Table
'SOURCE: www.TheSpreadsheetGuru.com
'Removing Filter, Columns, Rows
ActiveSheet.PivotTables("PivotTable1").PivotFields("Year").Orientation = xlHidden
'Removing Values
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Salaries").Orientation = xlHidden
End Sub
VBA删除透视表计算字段: Remove Calculated Pivot Fields
Sub RemoveCalculatedField()
'PURPOSE: Remove a calculated field from a pivot table
'SOURCE: www.TheSpreadsheetGuru.com
Dim pvt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
'Set Variable to Desired Pivot Table
Set pvt = ActiveSheet.PivotTables("PivotTable1")
'Set Variable Equal to Desired Calculated Data Field
For Each pf In pvt.DataFields
If pf.SourceName = "Inflation" Then Exit For
Next
'Hide/Remove the Calculated Field
pf.DataRange.Cells(1, 1).PivotItem.Visible = False
End Sub
报表按单个项目筛选:Report Filter On A Single Item
Sub ReportFiltering_Single()
'PURPOSE: Filter on a single item with the Report Filter field
'SOURCE: www.TheSpreadsheetGuru.com
Dim pf As PivotField
Set pf = ActiveSheet.PivotTables("PivotTable2").PivotFields("Fiscal_Year")
'Clear Out Any Previous Filtering
pf.ClearAllFilters
'Filter on 2014 items
pf.CurrentPage = "2014"
End Sub
透视表报表按多项筛选:Report Filter On Multiple Items
Sub ReportFiltering_Multiple()
'PURPOSE: Filter on multiple items with the Report Filter field
'SOURCE: www.TheSpreadsheetGuru.com
Dim pf As PivotField
Set pf = ActiveSheet.PivotTables("PivotTable2").PivotFields("Variance_Level_1")
'Clear Out Any Previous Filtering
pf.ClearAllFilters
'Enable filtering on multiple items
pf.EnableMultiplePageItems = True
'Must turn off items you do not want showing
pf.PivotItems("Jan").Visible = False
pf.PivotItems("Feb").Visible = False
pf.PivotItems("Mar").Visible = False
End Sub
VBA清除透视表报表筛选:Clear Report Filter
Sub ClearReportFiltering()
'PURPOSE: How to clear the Report Filter field
'SOURCE: www.TheSpreadsheetGuru.com
Dim pf As PivotField
Set pf = ActiveSheet.PivotTables("PivotTable2").PivotFields("Fiscal_Year")
'Option 1: Clear Out Any Previous Filtering
pf.ClearAllFilters
'Option 2: Show All (remove filtering)
pf.CurrentPage = "(All)"
End Sub
VBA刷新透视表:Refresh Pivot Table(s)
Sub RefreshingPivotTables()
'PURPOSE: Shows various ways to refresh Pivot Table Data
'SOURCE: www.TheSpreadsheetGuru.com
'Refresh A Single Pivot Table
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
'Refresh All Pivot Tables
ActiveWorkbook.RefreshAll
End Sub
VBA修改透视表数据源区域:Change Pivot Table Data Source Range
Sub ChangePivotDataSourceRange()
'PURPOSE: Change the range a Pivot Table pulls from
'SOURCE: www.TheSpreadsheetGuru.com
Dim sht As Worksheet
Dim SrcData As String
Dim pvtCache As PivotCache
'Determine the data range you want to pivot
Set sht = ThisWorkbook.Worksheets("Sheet1")
SrcData = sht.Name & "!" & Range("A1:R100").Address(ReferenceStyle:=xlR1C1)
'Create New Pivot Cache from Source Data
Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SrcData)
'Change which Pivot Cache the Pivot Table is referring to
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache (pvtCache)
End Sub
VBA设置透视表合计: Grand Totals
Sub PivotGrandTotals()
'PURPOSE: Show setup for various Pivot Table Grand Total options
'SOURCE: www.TheSpreadsheetGuru.com
Dim pvt As PivotTable
Set pvt = ActiveSheet.PivotTables("PivotTable1")
'Off for Rows and Columns
pvt.ColumnGrand = False
pvt.RowGrand = False
'On for Rows and Columns
pvt.ColumnGrand = True
pvt.RowGrand = True
'On for Rows only
pvt.ColumnGrand = False
pvt.RowGrand = True
'On for Columns Only
pvt.ColumnGrand = True
pvt.RowGrand = False
End Sub
VBA设置透视表报表布局:Report Layout
Sub PivotReportLayout()
'PURPOSE: Show setup for various Pivot Table Report Layout options
'SOURCE: www.TheSpreadsheetGuru.com
Dim pvt As PivotTable
Set pvt = ActiveSheet.PivotTables("PivotTable1")
'Show in Compact Form
pvt.RowAxisLayout xlCompactRow
'Show in Outline Form
pvt.RowAxisLayout xlOutlineRow
'Show in Tabular Form
pvt.RowAxisLayout xlTabularRow
End Sub
VBA格式化透视表的数据:Formatting A Pivot Table's Data
Sub PivotTable_DataFormatting()
'PURPOSE: Various ways to format a Pivot Table's data
'SOURCE: www.TheSpreadsheetGuru.com
Dim pvt As PivotTable
Set pvt = ActiveSheet.PivotTables("PivotTable1")
'Change Data's Number Format
pvt.DataBodyRange.NumberFormat = "#,##0;(#,##0)"
'Change Data's Fill Color
pvt.DataBodyRange.Interior.Color = RGB(0, 0, 0)
'Change Data's Font Type
pvt.DataBodyRange.Font.FontStyle = "Arial"
End Sub
VBA格式化透视表字段数据:Formatting A Pivot Field's Data
Sub PivotField_DataFormatting()
'PURPOSE: Various ways to format a Pivot Field's data
'SOURCE: www.TheSpreadsheetGuru.com
Dim pf As PivotField
Set pf = ActiveSheet.PivotTables("PivotTable1").PivotFields("Months")
'Change Data's Number Format
pf.DataRange.NumberFormat = "#,##0;(#,##0)"
'Change Data's Fill Color
pf.DataRange.Interior.Color = RGB(219, 229, 241)
'Change Data's Font Type
pf.DataRange.Font.FontStyle = "Arial"
End Sub
VBA展开/收缩整个字段细节:Expand/Collapse Entire Field Detail
Sub PivotField_ExpandCollapse()
'PURPOSE: Shows how to Expand or Collapse the detail of a Pivot Field
'SOURCE: www.TheSpreadsheetGuru.com
'office中国交流网翻译
Dim pf As PivotField
Set pf = ActiveSheet.PivotTables("PivotTable1").PivotFields("Month")
'Collapse Pivot Field
pf.ShowDetail = False
'Expand Pivot Field
pf.ShowDetail = True
End Sub