Refresh row headers in pivot tables with vba

[__ndt__]

Hello,

I need to refresh (i.e. in this case: use the SourceName again) row headers in pivot tables with vba.

I found code (probably written with a former version af Excel):

Public Sub piv_kor()
Dim wS As Worksheet
Dim pT As PivotTable
Dim pF As PivotField
Dim pI As PivotItem
 
For Each wS In ActiveWorkbook.Worksheets
    For Each pT In wS.PivotTables
    pT.PivotCache.Refresh
        For Each pF In pT.PivotFields
            For Each pI In pF.PivotItems
                pI.Name = pI.SourceName
            Next pI
        Next pF
    Next pT
Next wS
End Sub

I get a runtime error (Type mismatch (Error 13). As I understand it from the Microsoft documentation, pI.Name is a string, whereas pI.SourceName is a variant. If I use pI.SourceNameStandard (which is also a string), the code works fine, but i get american date formats (some of my row headers are dates, and I need them to stay in the format of my region DD.MM.YYYY).

I tried to convert the variant to string using the following code, but I then get a runtime error (Invalid procedure call or argument (Error 5):

...
pI.Name = Cstr(pI.SourceName)
...

I guess I am doing something wrong with the conversion. Any help would be much appreciated!

Andreas

---
I am using Excel 2007 on a Terminal Server (Microsoft Windows Server 2008 R2 Enterprise). I am new to vba, though I do programming in SQL and statistical software packages (SPSS, Stata), so I have an OK grasp of programming concepts.

 

[__ndt__]

Can I provide additional information for my problem?