Problem with on error goto, in pivot table macro - I still get an error

[akzephyr]

hi

I am trying to create an automated script to automatically change multiple examples of the same field dropped into a pivot table into defined calculations of that field. eg change the first instance into average, second into stdev, 3rd into count etc.

This requires them to be renamed to avoid an error, but I am trying to use the error as a tool to jump to a part of the code that will set them as a different calculation (and name).

This works until it tries convert the 2nd or 3rd example of the field, when it seems to ignore the on error goto instruction and throw up "Run-time error 1004 PivotTable field name already exists".

Can anyone help?

Sub SetPivotFieldsAutomatically()

   Dim pvtFld As Excel.PivotField
   Dim pt As PivotTable

    With Selection.PivotTable
        .ManualUpdate = True
        
For Each pvtFld In .DataFields
            With pvtFld
                On Error GoTo x1
                .Function = xlAverage
                .NumberFormat = "0.0"
                .Caption = "av." & pvtFld.SourceName
                GoTo x3

x1:
                On Error GoTo x2
                .Function = xlStDev
                .NumberFormat = "0.00"
                .Caption = "sd." & pvtFld.SourceName
                GoTo x3

x2:
.Function = xlcount
                .NumberFormat = "0"
                .Caption = "n." & pvtFld.SourceName

x3:
End With
    Next pvtFld
    End With

End Sub

 

[Fluff]

VBA can only handle one error at a time. Which is why you are gettingerror messages.
Have a look here for info on error handling
http://excelmatters.com/2015/03/17/on-error-wtf/

 

[akzephyr]

thank you fluff. I tried clearing the error with resume next statements, but then I ended up in a loop with the macro not completing.

Any ideas on getting through this?

 

[Fluff]

Try

x1:
on error goto -1
                On Error GoTo x2
                .Function = xlStDev
                .NumberFormat = "0.00"
                .Caption = "sd." & pvtFld.SourceName
                GoTo x3