VBA for changing all field settings in a Pivot Table

[JoeMontana]

Hi there,

I have a pivot table that has about 35 columns and I would like to quickly change all of the field settings (eg. from sum to max)

The macro that I have works in principle, but it crashes every time after two or three fields are changed. Any suggestions for improvements or workarounds?

The key part is the For Loop is:

For Each pf In pt.DataFields
      pf.Function = xlCount
    Next pf

My full script is:

Sub ChangeAllValueFields()
  Dim pt As PivotTable
  Dim pf As PivotField
  Dim ws As Worksheet
  Dim FieldSetting As String


  Set ws = ActiveSheet
  Set pt = ws.PivotTables(1)
  
FieldSetting = InputBox("Enter field setting for pivotchart" & vbCrLf & vbCrLf & "xlCount" & vbCrLf & "xlSum" & vbCrLf & "xlMin" & vbCrLf & "xlMax")
  
  Application.ScreenUpdating = False
  pt.ManualUpdate = True


If FieldSetting = "xlCount" Then


    
    For Each pf In pt.DataFields
      pf.Function = xlCount
    Next pf
 




ElseIf FieldSetting = "xlSum" Then


     For Each pf In pt.DataFields
      pf.Function = xlSum
    Next pf


ElseIf FieldSetting = "xlMin" Then


   
    For Each pf In pt.DataFields
      pf.Function = xlMin
    Next pf
  
ElseIf FieldSetting = "xlMax" Then


   
    For Each pf In pt.DataFields
      pf.Function = xlMax
    Next pf
   


Else
MsgBox ("Entry not on list. Changes cancelled")
End If


  pt.ManualUpdate = False
  Application.ScreenUpdating = True
  
  Set pf = Nothing
  Set pt = Nothing
  Set ws = Nothing
End Sub

 

[RoryA]

Welcome to the forum.

In what way does it crash? With an error message, or Excel freezing/crashing? Something else?

 

[JoeMontana]

Thanks Rory,

I'm a long time listener, first time caller

Excel crashes / stops working. Like it appears to be a memory issue.

The script will also run fully through without crashing if I step through each line. But then it only changes the first approx 10 fields. I don't know anything about the backend of excel but again, it seems like it just runs out of memory and stops executing the script.

I tried it with a smaller table, with 3 fields and it works fine.

Would adding a Wait line in the loop help?

I'm using 64-bit Office 365 ProPlus

 

[RoryA]

You could try adding DoEvents to the loop:

    For Each pf In pt.DataFields
      pf.Function = xlMax
      DoEvents
    Next pf

 

[JoeMontana]

Thanks Rory, that's a new command for me.

Unfortunately Excel still stops working if I try to run it or step into too quickly.

 

[RoryA]

Is your pivot table using the data model?

 

[JoeMontana]

Yes, It's two tables joined by Dates

 

[RoryA]

I wonder if that could be the issue. What happens if you don't use pt.manualupdate in the code?