Excel VBA数据透视表向导，设置表目标并删除总计 [英] Excel VBA Pivot Table Wizard, set table destination and remove grand totals

问题描述

下面的方法效果很好，但是我希望目标工作表是一个名为"PIVOT"的特定现有工作表.我也想删除rows和columns的总计.有任何想法吗?

The below works well, however I want the destination sheet to be a specific existing sheet called "PIVOT". Also I would like to remove grand totals for both rows and columns. Any ideas?

Sub CreatePivot()
' Creates a PivotTable report from the table on Sheet1
' by using the PivotTableWizard method with the PivotFields
' method to specify the fields in the PivotTable.
Dim objTable As PivotTable, objField As PivotField

' Select the sheet and first cell of the table that contains the data.
ActiveWorkbook.Sheets("DATA").Select
Range("A1").Select

' Create the PivotTable object based on the Employee data on Sheet1.
Set objTable = ActiveWorkbook.Sheets("DATA").PivotTableWizard

' Specify row and column fields.
Set objField = objTable.PivotFields("CCY")
objField.Orientation = xlRowField

Set objField = objTable.PivotFields("ACC")
objField.Orientation = xlRowField

Set objField = objTable.PivotFields("VD")
objField.Orientation = xlColumnField

' Specify a data field with its summary
' function and format.
Set objField = objTable.PivotFields("AMOUNT")
objField.Orientation = xlDataField
objField.Function = xlSum
objField.NumberFormat = "#,##0"

End Sub

推荐答案

目标工作表可以由PivotTableWizard的TableDestination参数定义，然后可以使用ColumnGrand和RowGrand删除总计. .这是更改后的代码:

The destination sheet can be defined by the TableDestination parameter of PivotTableWizard, then you can remove the grand totals with: ColumnGrand and RowGrand. Here is the changed code:

Sub CreatePivot()
' Creates a PivotTable report from the table on Sheet1
' by using the PivotTableWizard method with the PivotFields
' method to specify the fields in the PivotTable.
Dim objTable As PivotTable, objField As PivotField

' Select the sheet and first cell of the table that contains the data.
ActiveWorkbook.Sheets("DATA").Select
Range("A1").Select

' Create the PivotTable object based on the Employee data on PIVOT with the name 'PivotTableName'.
Set objTable = ActiveWorkbook.Sheets("DATA").PivotTableWizard(TableDestination:="PIVOT!R1C1", TableName:="PivotTableName")

'Remove grand totals
With Sheets("PIVOT").PivotTables("PivotTableName")
    .ColumnGrand = False
    .RowGrand = False
End With

' Specify row and column fields.
Set objField = objTable.PivotFields("CCY")
objField.Orientation = xlRowField

Set objField = objTable.PivotFields("ACC")
objField.Orientation = xlRowField

Set objField = objTable.PivotFields("VD")
objField.Orientation = xlColumnField

' Specify a data field with its summary
' function and format.
Set objField = objTable.PivotFields("AMOUNT")
objField.Orientation = xlDataField
objField.Function = xlSum
objField.NumberFormat = "#,##0"

End Sub

这篇关于Excel VBA数据透视表向导，设置表目标并删除总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！