ateneva
4/16/2017 - 4:04 PM

Insert blank space between each upper characters in a PivotTable Datafield name

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