VBA Pivot Item.Name Pulling Wrong Value

Question

I built this code and it works for the first four filtered items. On the fifth it stops working. It is also pulling the wrong name. I have refreshed the PIVOT table and it shows 'Ft Lauderdale, FL' but when the VBA debugger goes off and I hover over piOffice.Name it shows 'Ft Lauderdal, FL'. This was the old name before I fixed it. I also tried different variations so there were no spaces (e.g. Ft_Lauderdale,FL). Each time I still get Runtime Error Code 5 and when I hover over piOffice.Name it still shows 'Ft Lauderdal, FL'.

Sub Deferred_Rent_To_PDF()

Dim strWorkbook As String
Dim strWorksheet As String
Dim strPivotTable As String
Dim pdfFilename As Variant
Dim strPivotFilter As String
Dim strDocName As String
Dim ptDeferredRent As pivotTable
Dim piOffice As PivotItem

strWorkbook = "Schedule of Leases - Beta"
strWorksheet = "Deferred"
strPivotTable = "DeferredRent"

Workbooks(strWorkbook).Activate
Set ptDeferredRent = Worksheets(strWorksheet).PivotTables(strPivotTable)

    For Each piOffice In ptDeferredRent.PageFields("Office").PivotItems
        ptDeferredRent.PageFields("Office").CurrentPage = piOffice.Name   '<---------- ISSUE IS HERE
        strPivotFilter = Worksheets(strWorksheet).Range("H1")
        strDocName = "Deferred Rent - " & strPivotFilter & " - " & Format(Date, "mm-dd-yy")
        pdfFilename = Application.GetSaveAsFilename(InitialFileName:=strDocName, _
            FileFilter:="PDF, *.pdf", Title:="Save As PDF")

            ActiveSheet.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=pdfFilename, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=False, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=False

    Next piOffice

End Sub

Answer 1

The reason you are getting Run Time error 5: Invalid procedure call or argument is because the PivotCache is still retaining the "old name", regardless of the PivotCache been refreshed.

In order to solve this issue you need to change the PivotCache.MissingItemsLimit property (Excel). This are the valid values XlPivotTableMissingItems enumeration (Excel). I suggest to change it to: xlMissingItemsNone by adding this line:

ptDeferredRent.PivotCache.MissingItemsLimit = xlMissingItemsNone

inmmediately after this line:

Set ptDeferredRent = Worksheets(strWorksheet).PivotTables(strPivotTable)

Your revised code will be like this:

Set ptDeferredRent = Worksheets(strWorksheet).PivotTables(strPivotTable)
ptDeferredRent.PivotCache.MissingItemsLimit = xlMissingItemsNone