bradyboyy88
Well-known Member
- Joined
- Feb 25, 2015
- Messages
- 562
Hi,
As the title says I am trying to have my pivot table use the format for each field that its source data uses.
I have used this code to change the names of each of the pivotfields in the pivot table and would preferably like a way to do this using the for loop indicated below when changing the names. Is there anyway to throw this in? Thanks
Dim pt As PivotTable
Dim pf As PivotField
'some of the commands may cause VBA errors in some situations
'- these will be ignored as they are not important
On Error Resume Next
'capture the selected pivot table
Set pt = ActiveSheet.PivotTables("SummaryReport")
For Each pf In pt.DataFields
'add a space to the data field name because Excel won't
'allow the use of a data field name within the pivot table
pf.Caption = pf.SourceName & " "
Next pf
'reset error handling
On Error GoTo 0
'clear object variables
Set pt = Nothing
Set pf = Nothing
As the title says I am trying to have my pivot table use the format for each field that its source data uses.
I have used this code to change the names of each of the pivotfields in the pivot table and would preferably like a way to do this using the for loop indicated below when changing the names. Is there anyway to throw this in? Thanks
Dim pt As PivotTable
Dim pf As PivotField
'some of the commands may cause VBA errors in some situations
'- these will be ignored as they are not important
On Error Resume Next
'capture the selected pivot table
Set pt = ActiveSheet.PivotTables("SummaryReport")
For Each pf In pt.DataFields
'add a space to the data field name because Excel won't
'allow the use of a data field name within the pivot table
pf.Caption = pf.SourceName & " "
Next pf
'reset error handling
On Error GoTo 0
'clear object variables
Set pt = Nothing
Set pf = Nothing
