Macro Help

[douglascfast]

All,

This macro was supplied by a member of the group: Debra Dalgleish

Very helpfull

Sub ResetCaptions()
'retrieve original field names
'if captions have been typed into pt
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveSheet.PivotTables(1)
For Each pf In pt.VisibleFields
For Each pi In pf.PivotItems
pi.Caption = pi.SourceName
Next pi
Next pf
pt.RefreshTable
End Sub

I need to do this for only one pivotitem for all pivot tables on the
active sheet. I dont sem to be able to get this to work though I am
sure it is simple.

Can someone help?

Doug

 

[Debra Dalgleish]

You could use code similar to the following:

'=====================Sub ResetCaptionsOneItem()
'retrieve original field names
'if captions have been typed into pt
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim strField As String
Dim strItem As String
strField = "Region"
strItem = "East"

For Each pt In ActiveSheet.PivotTables
Set pf = pt.PivotFields(strField)
For Each pi In pf.PivotItems
If pi.SourceName = strItem Then
pi.Caption = pi.SourceName
Exit For
End If
Next pi
pt.RefreshTable
Next pt
End Sub
'=================

 

[douglascfast]

Debra,

Thanks for this. I had to modify it just a bit, but it is not changing
the field I wanted. The column name is "UPLMTH" and the new name in
the privotable is "Uplift Month" I want to change the caption (APR) to
(4) the original source data.

Here is the code I used

Sub ResetCaptions1()
'retrieve original field names
'if captions have been typed into pt

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim SFld As String
SFld = "Uplift month"

For Each pt In ActiveSheet.PivotTables
Set pf = pt.PivotFields(SFld)

For Each pi In pf.PivotItems
pi.Caption = pi.SourceName
Exit For

Next pi
pt.RefreshTable
Next pt
End Sub

 

[Debra Dalgleish]

With the changes you've made, you won't need the Exit For line.
Remove that, and the captions should change back to the source name.