Change Pivot Items via VBA does not work

[jaryszek]

Hi Guys,

i have Source data with dates from 01.01.2020 until 30.12.2020.

From this i created pivot table and what want to do is to change all Pivot Items names to corresponding month.

Using this code:

Please Login or Register  to view this content.

and this is working only for first row!
Why? Can anybody help?
Very strange.

Best,
Jacek

[Vraag en antwoord]

Take this away.

Please Login or Register  to view this content.

The name of a pivot item must be unique.

[jaryszek]

Thank you!

what i am trying to do here is gruop by months:

Screenshot_70.png

but when i am running code after grouping it is not working....

Can you please help?
Jacek

[rorya]

That code wouldn't make any sense after grouping by month as far as I can see.

[jaryszek]

yes this is not having sense at all.

But Rory, it is not possible to change 3 letter month to defined by user besides changing XML file ;/

And i do not want to do this...

Jacek

[rorya]

I don't know what you're talking about I'm afraid.

[jaryszek]

Sorry Rory,

i am talking about this issue which i though that will solve with the topic approach:

https://www.excelcampus.com/pivot-ta...ld-formatting/

To change Jan name after grouping date by month to January.
But Excel uses default which is saved in xml file.

Best,
Jacek

[rorya]

Yes, but as shown in that link, you can change the month names after grouping. Not sure why you would want to though.

[jaryszek]

The code from link is not working because you can not have duplicates and you are changing 01.01.2020 to Jan, and want to change also 02.02.2020 but here you will get error because of duplicate.
It is not looping through months but for detailed date.

Best,
Jacek

[rorya]

Like I said, you can change the month name after grouping. It wouldn't make sense to do it beforehand.

[jaryszek]

Hi Rory!

Thank you for saying that, can you help with it?

The code from link is not working from me. How can i do this?

Jacek

[rorya]

What exactly do you want as the result (and why)?

[jaryszek]

I want to have instead of image above months names get:

Sty - Styczeń (Jan - January)
Lut - Luty (Feb - February)

So after grouping by month i want to change labels for months.
As default you get 3 letters for month. I do not want that - this is ugly and want to have nice name - fully name.

Jacek

[rorya]

Do you have a particular reason for not just adding a month name column to your source data/query? It makes life a lot easier.

If you really need code, just group the months then use something like:

Please Login or Register  to view this content.

[jaryszek]

thank you!!!

Working!

Jacek