PivotField Renaming

[chirayu]

Hi All,

I had a question to do with the Values bit of the PivotTable. Basically when a PivotField is part of the Values in a PivotTable it usually comes up with "Sum of" or "Average of" Prefix etc.

What I wanted to know is, is there a way to pull the PivotField name from the Values part of the PivotTable into a variable without this Prefix?

Or would I need to code in multiple IFs to remove the Prefix before using the PivotField as a variable to filter the PivotTable?

 

[Chihiro]

Can you clarify? I'm a bit confused as to what you want.

Is it that you have "Sum of xxx" but want to extract "xxx" part and put it into variable?

 

[chirayu]

@Chihiro

Yes. But its made more complicated by the fact that it isn't necessarily "Sum of xxx" it could be any of the "Summarize Value field by" functions available in the PivotTable.

So I was wondering if there is a way to get the xxx without the Prefix.

Or do I need to do something like:

IF x like "Sum of *" Then ...

 

[chirayu]

@Chihiro found the solution. thanks anyways. link below

http://www.contextures.com/xlPivot02.html#renamemacro

 

[Chihiro]

You can use split function or RegEx, but since I'm not familiar with VBA RegEx syntax and methods... I'll show split function.

Something like below.

Sub Demo()
Dim sString As String
Dim x As Variant

sString = "Sum of xxx"
x = Split(sString, " of ")
Debug.Print x(UBound(x))

sString = "Average of xxx"
x = Split(sString, " of ")
Debug.Print x(UBound(x))

End Sub

xxx is returned in both case.

 

[shrivallabha]

Are you looking for SourceName?

Public Sub PTVal()
Dim pt As PivotTable
Dim dt As PivotField
For Each pt In Sheets(1).PivotTables
    For Each dt In pt.DataFields
        MsgBox "Field Caption is: " & dt.Caption & " & Source Header Name is: " & dt.SourceName
    Next
Next
End Sub

 

[chirayu]

@Chihiro @shrivallabha figured it out already (as per link in my previous post)

Yeah I was looking for PivotField.SourceName (I was using PivotField.Caption but that would add the "Sum of"/ "Count of" etc on which I didn't want in my Variable

Both of your solutions are good but went down the same route as shrivallabha & contextures link I posted earlier in the thread. less of a coding hassle to remove the prefix.

 

[shrivallabha]

@chirayu, I was also curious so I was looking at the object properties. I saw your post but then decided to post anyway what I had worked on.

@Chihiro built-in method carries merit besides ease of coding. Let's say if the field already contains " of " then above method will give unexpected results.

Following should work assuming we get " of " all the time (I am not a regular user of Pivots) and that'd be better than building if-loop construct covering possibilities that Chirayu hinted in 3rd post of this thread.

Sub tp()
Dim strinput As String, stroutput As String
'\\Field Count of Apples
strinput = "Sum of Count of Apples"
x = Split(strinput, " of ")
stroutput = x(UBound(x))
MsgBox stroutput
stroutput = Mid(strinput, InStr(1, strinput, " of ", vbTextCompare) + 4, Len(strinput))
MsgBox stroutput
End Sub