PivotTable Settings Options?

[DougStroud]

Under PivotTable Fields, Subtotal, there are three options, Automatic, Custom and None. Automatic is the default setting, can the defalut setting be changed to None?

And upon creating a new PivotTable, I need to bring every field into the PivotTable from the field list, and there a quite a few. Can multiple fields be selected and populate the PivotTable?

thanks,

doug

 

[mrhartley]

Hi,
As far as I know, you cannot change the subtotal default setting.

There is an Excel add-in for Pivot Tables, around here somewhere which can be used to set subtotals to none quickly, might be worth a try.

I have never tried adding more than one data field into the pivot table at time. I would be interested to hear of anyone that has done this.

Regards
Mark

 

[DougStroud]

I looked up a couple of the add-ins for Excel but did not locate any that would allow to alter the setting from Automatic to None.

Thanks for the reply.

Doug

 

[mrhartley]

Hi,

I took this out of an Excel add in, it might be of use to you.

Now you need to do the following in your personal.xls file if you want it available for every worksheet otherwise you need to do the following for any given worksheet.

Not sure how useful this is to you but here goes.

View code and Add 3 new modules.
In one of the modules add the following code:

Sub HideAllSubtotals()
Dim pt As PivotTable
Dim pf As PivotField
Dim ws As Worksheet
Set ws = ActiveSheet
Dim bPTBar As Boolean
Dim iPTBarPos As Integer
On Error GoTo errHandler

iPTBarPos = Application.CommandBars("PivotTable").Position

bPTBar = Application.CommandBars("PivotTable").Visible

If PivotCheck(ws) Then
  On Error Resume Next
  Application.ScreenUpdating = False
  For Each pt In ActiveSheet.PivotTables
    pt.ManualUpdate = True
    For Each pf In pt.PivotFields
      pf.Subtotals(1) = True
      pf.Subtotals(1) = False
    Next pf
    pt.ManualUpdate = False
  Next pt
Else
  MsgBox "There are no pivot tables on the active sheet"
End If

exitHandler:
  Set pf = Nothing
  Set pt = Nothing
  Set ws = Nothing
  Application.ScreenUpdating = True
  Application.CommandBars("PivotTable").Visible = bPTBar
  Application.CommandBars("PivotTable").Position = iPTBarPos
  Exit Sub

errHandler:
  GoTo exitHandler
End Sub

In the next module add the following code

Sub ShowAllSubtotals()
Dim pt As PivotTable
Dim pf As PivotField
Dim ws As Worksheet
Dim bPTBar As Boolean
Dim iPTBarPos As Integer
On Error GoTo errHandler

iPTBarPos = Application.CommandBars("PivotTable").Position

bPTBar = Application.CommandBars("PivotTable").Visible
Set ws = ActiveSheet

If PivotCheck(ws) Then
  On Error Resume Next
  Application.ScreenUpdating = False
  For Each pt In ActiveSheet.PivotTables
    pt.ManualUpdate = True
    For Each pf In pt.PivotFields
      pf.Subtotals(1) = True
    Next pf
    pt.ManualUpdate = False
  Next pt
Else
  MsgBox "There are no pivot tables on the active sheet"
End If

exitHandler:
  Set pf = Nothing
  Set pt = Nothing
  Set ws = Nothing
  Application.ScreenUpdating = True
  Application.CommandBars("PivotTable").Visible = bPTBar
  Application.CommandBars("PivotTable").Position = iPTBarPos
  Exit Sub

errHandler:
  GoTo exitHandler
End Sub

In the last module add the following code

Option Explicit

Public strProcedure As String

Function PivotCheck(ws As Worksheet) As Boolean
  
  PivotCheck = False
  
  If ws.PivotTables.Count > 0 Then
    PivotCheck = True
  End If
  
End Function

You will now have two macros at your finger tips. You should be able to add the macro hideallsubtotals and the macro showallsubtotals to buttons which you can add to one of your tool bars.
No worries if this is no good as I liked playing around to get this to work anyway.

Regards
Mark