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
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
