CODEWITHSUNDEEP
vbaexcelpivot-table
Ibby
Ibby

Reputation: 96

Change column titles based on source name in a Pivot Table using VBA

I am writing code to loop through each of the pivot fields in a pivot table and then change the "custom name" based on the source name. When I record changing a column header I receive the following code:

Range("J5").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Man-Hours Oct-16").Caption _
    = "Man-Hours Nov-16"

The issue is, the custom name changes month to month, so I want to refer to the pivot field based on the source name, not the caption. I wrote a code that successfully loops through each pivot field and identifies the source, however, I receive an error message when it attempts to modify the caption:

Dim CurrentDate As Date
Dim CurrentMonth As String
Dim CurrentYear As String
Dim pf as PivotField

CurrentDate = Range("CurrentDate")
CurrentMonth = Format(CurrentDate, "MMM")
CurrentYear = Format(CurrentDate, "YY")

For Each pf In PivotSheet.PivotTables("PivotTable1").PivotFields
    'Total Forecast
    If pf.SourceName = "Man-Hours 0" Then
        pf.Caption = "Man-Hours " & CurrentMonth & "-" & CurrentYear
    ElseIf pf = "Cost ($$) 0" Then
        pf.Caption = "Cost ($$) " & CurrentMonth & "-" & CurrentYear  
    End If
Next pf

The above code receives the error: "Run-time error '5': Invalid procedure call or argument" on the pf.Caption line.

I have also attempted to use pf.LabelRange as well and I receive the error: "Run-time error '1004': Unable to get the LabelRange property of the PivotField class"

I have been searching around trying to find solutions. I haven't written much code with pivot tables so I'm not sure if I should be using pivot items? All the code I have found that changes the column headers does it based on the previous caption name.

Upvotes: 2

Views: 6445

Answers (1)

Ibby
Ibby

Reputation: 96

After modifying the code a few times, I solved the issue. Just posting in case anyone has a similar issue. Rather than looping through the PivotFields I should have looped through DataFields. The below modified code works:

Dim CurrentDate As Date
Dim CurrentMonth As String
Dim CurrentYear As String
Dim pf as PivotField

CurrentDate = Range("CurrentDate")
CurrentMonth = Format(CurrentDate, "MMM")
CurrentYear = Format(CurrentDate, "YY") 

For Each pf In PivotSheet.PivotTables("PivotTable1").DataFields
    'Total Forecast
    If pf.SourceName = "Man-Hours 0" Then
        pf.Caption = "Man-Hours " & CurrentMonth & "-" & CurrentYear
    ElseIf pf.SourceName = "Cost ($$) 0" Then
        pf.Caption = "Cost ($$) " & CurrentMonth & "-" & CurrentYear
    End If
Next pf

If anyone can comment on why DataFields works rather than PivotFields, I am curious to know!

Upvotes: 1

Related Questions