Change row label in Pivot Table with VBA

tfield98

New Member
Joined
Aug 30, 2011
Messages
35
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
pivottables(1).pivotfields("Manufacturer").caption = "new name"
 

tfield98

New Member
Joined
Aug 30, 2011
Messages
35
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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

New Member
Joined
Aug 30, 2011
Messages
35

ADVERTISEMENT

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.

pivot2.jpg
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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
Code:
ActiveSheet.PivotTables("PivotTable9").PivotFields("Floor").Orientation = xlHidden
With ActiveSheet.PivotTables("PivotTable9").PivotFields("Zone")
    .Orientation = xlRowField
    .Position = 2
End With
 

tfield98

New Member
Joined
Aug 30, 2011
Messages
35
Ah ha! <blushing> I forgot about recording. Your sample and a recording got me what I needed. Thanks, Fazza, for your help!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,368
Messages
5,601,220
Members
414,434
Latest member
Riyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top