Hide subtotals of rowfields in a pivot table
 

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

Hide subtotals of rowfields in a pivot table
 

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/

Hide subtotals of rowfields in a pivot table
 

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

Hide subtotals of rowfields in a pivot table
 

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