Change row label in Pivot Table with VBA

[tfield98]

I have a pivot table with two row labels (that appear as columns)

How can I in VBA change the "Row Label" name "Manufacturer" to another name that exists in the Fields list, say, "Country" I'm thinking I don't want to just delete the field if I can avoid it because

• I have this need in another pivot table that has 3 columns and I'm still changing column 2.
• I want to retain the properties, just change the name.

TIA

 

[Fazza]

pivottables(1).pivotfields("Manufacturer").caption = "new name"

 

[tfield98]

pivottables(1).pivotfields("Manufacturer").caption = "new name"

Thank you, Fazza.

However, when I try your suggestion, I get "Run-time error '5' Invalid procedure call or argument."

I'm able to print the left side of the expression in the immediate window, so I've got that syntax right.

Will the code you suggested change the column heading label or the actual field in the Field List? I'm trying to do the latter.

Thank you for your help!

 

[Fazza]

I'm confused as to exactly what is wanted. It doesn't make sense to me - I must have it wrong: it seems there are fields Manufacturer & Country and you want to rename Manufacturer to Country? That is to a field name that already exists??

In all or only some pivot tables?

Not sure if relevant, but the very first sentence confuses me too "I have a pivot table with two row labels (that appear as columns)". If they are row labels they do not appear as columns, they would be column fields/labels. If they appear as columns they are not row labels.

If you want to change a field name between the source table and the pivot table I suggest you do this in SQL. So if the source data has fields Type and Manufacturer but you want them to be Type and Country in the pivot table it'd be like this,

SELECT Type, Manufacturer AS [Country]
FROM your_source_data

I don't know what happens if you already have a field called Country: it will either error or not: suggest you try it & see.

HTH. regards

PS. Adding a sheet reference to the earlier code I gave might overcome the error message. Note, I use Excel 2003. If you're in a newer version just check if the object references are the same.

 

[tfield98]

Yes, Fazza. I can understand how you'd be confused. It was confusing to me too that the "Row Labels" in the Field List section showed up as Column labels. I want to uncheck one field and check another, as shown below.

My apologies to you for not spelling it out more clearly in my original post.

 

[Fazza]

That is a huge help, thank you. Suggest you try using the macro recorder: I think it'll generate some OK code.

I tried it just now, Excel 2003, and got something like this. HTH

ActiveSheet.PivotTables("PivotTable9").PivotFields("Floor").Orientation = xlHidden
With ActiveSheet.PivotTables("PivotTable9").PivotFields("Zone")
    .Orientation = xlRowField
    .Position = 2
End With

 

[tfield98]

Ah ha! <blushing> I forgot about recording. Your sample and a recording got me what I needed. Thanks, Fazza, for your help!

 

[Fazza]