Change Pivot Items via VBA does not work

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:

Option Explicit

Sub test()

Dim pt As PivotTable

With Sheet2

Set pt = .PivotTables(1)

Dim pi As PivotItem

With pt
  For Each pi In .PivotFields("Dates").PivotItems
    
    Dim monthNaming As String
    Dim MonthFormat As String
    On Error Resume Next
    
        MonthFormat = Format(pi.Name, "yyyy-dd-mm")
        monthNaming = StrConv(MonthName(CLng(Month(CDate(MonthFormat)))), vbProperCase)
        If Err.Number = 0 Then
            pi.Name = monthNaming
        End If
    On Error GoTo 0
    
  Next pi
End With
  
End With

End Sub

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

Best,
Jacek

Take this away.

On Error resume Next

The name of a pivot item must be unique.

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

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

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

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

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

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

thank you!!!

Working!

Jacek