Hi, all, I use VBA to generate a pivot table and Excel adds subtotal to all
row field headers. How can I hide all subtotals in VBA? I tried using
Excel's macro recording function, but it only delete single cell which
contain the subtotal header. Thanks

Huyeote

Right click grey column header to get menu.
Field - SubTotals ... none
Options - uncheck Grand totals for columns/Grand totals for rows

With pivot tables it is always best to record macros because the
requirements are not exactly intuitive.


---
Message posted from http://www.ExcelForum.com/

The following code will turn off the subtotals for all row and column
fields:

'=========================
Sub NoSubtotals()
'turns off subtotals in pivot table
'.PivotFields could be changed to
'.RowFields or .ColumnFields
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.PivotFields
'First, set index 1 (Automatic) to True,
'so all other values are set to False
pf.Subtotals(1) = True
pf.Subtotals(1) = False
Next pf
Next pt
End Sub
'========================

Huyeote wrote:
Hi, all, I use VBA to generate a pivot table and Excel adds subtotal to all
row field headers. How can I hide all subtotals in VBA? I tried using
Excel's macro recording function, but it only delete single cell which
contain the subtotal header. Thanks

Huyeote




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

It works perfectly, thanks.


"Debra Dalgleish" wrote in message
...
The following code will turn off the subtotals for all row and column
fields:

'=========================
Sub NoSubtotals()
'turns off subtotals in pivot table
'.PivotFields could be changed to
'.RowFields or .ColumnFields
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.PivotFields
'First, set index 1 (Automatic) to True,
'so all other values are set to False
pf.Subtotals(1) = True
pf.Subtotals(1) = False
Next pf
Next pt
End Sub
'========================

Huyeote wrote:
Hi, all, I use VBA to generate a pivot table and Excel adds subtotal to
all
row field headers. How can I hide all subtotals in VBA? I tried using
Excel's macro recording function, but it only delete single cell which
contain the subtotal header. Thanks

Huyeote




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html