Insert blank space between each upper characters in a PivotTable Datafield name
Sub InsertBlankSpacesBetweenUpperCharactersInName()
Dim Wks As Worksheet
Dim PT As PivotTable
Dim PF As PivotField
Dim mStr As String
Dim i As Integer
Dim FindUpper As Integer
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'written by Angelina Teneva, Feb 2017; assumes the characters has only two upper characters
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For Each Wks In ActiveWorkbook.Worksheets
For Each PT In Wks.PivotTables
On Error Resume Next
For Each PF In PT.DataFields
mStr = PF.Caption
For i = 2 To Len(mStr)
If Mid(mStr, i, 1) Like "[A-Z]" Then
FindUpper = i
PF.Caption = Left(mStr, FindUpper - 1) & Chr(32) & _
Right(mStr, Len(mStr) - FindUpper + 1)
Exit For
End If
Next i
Next PF
Next PT
Next Wks
End Sub