Search code examples
excelvbapowerpivot

VBA - Renaming column titles/names on power pivot table

The GetMeasure caption parameter does not seem to affect the column name of the pivot table. In a regular pivot table, the caption can be used to rename the title of the column. I cannot figure out how to rename the default sum column names for a power pivot table. I need to change the default column names "Sum of RS Acad FTE" and "Sum of RS Vocational FTE" to "Acad FTE" and "Vocational FTE"

Here is my code snippet. The last parameter in the GetMeasure method is the caption attribute that seem to have no affect on the column headings.

Set cf = pt_enroll_summary.CubeFields.GetMeasure("[EnrollmentTableRange].[RS Acad FTE]", xlSum, "Academic FTE")
pt_enroll_summary.AddDataField cf

Set cf = pt_enroll_summary.CubeFields.GetMeasure("[EnrollmentTableRange].[RS Vocational FTE]", xlSum, "Vocational FTE")
pt_enroll_summary.AddDataField cf

Screenshot

Solution

  • I could not figure out how to directly manipulate the column heading/title via a cubefield attribute/method, but here is the workaround I used to find the cell position of the heading to changing the value.

    Dim labelColumn As Long
Dim labelAddress As String

Set cf = pt_enroll_summary.CubeFields.GetMeasure("[EnrollmentTableRange].[RS Acad FTE]", xlSum)
pt_enroll_summary.AddDataField cf
labelColumn = cf.PivotFields.Item(1).CubeField.Position
labelAddress = pt_enroll_summary.TableRange1.Cells(1, 1).Offset(0, labelColumn).Address
ActiveSheet.range(labelAddress).Value = "Acad FTE"