If you happen to be retrieving the data for your analysis by directly connecting Excel to a database, you might often encounter fields that have no spaces between their distinct words.
Whilst this is indeed best practice when writing your SQL query (having to deal with spaces in SQL aliases can be a very annoying matter), it is anything but user friendly for the people reading your spreadsheets.
Should you (or the analyst who had written the query) have stuck to the pattern of starting each new word in the field name with a capital letter, there’s a very sleek way, which you could use to apply some cosmetic tweaks on your subsequent pivot tables.
NB! The code below does assume that the pivot field names are only composed of two distinct words, each of which starts with a capital letter.
How does it work?
The VBA-routine will loop through all the value fields in all the pivot tables of your spreadsheet and find the second capital letter in the pivot field name. Then it will replace the name of the pivot field with one that has a space right before the second capital letter in the string.
|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)|
Happy VBA coding!