Can't seem to hide the "oldest" year/month code in a pivot table via VBA

[dougbert]

Hi all,

I’m using Excel 2013. I swear my VBA code used to work on pivot tables under Excel 2010, but it hasn’t worked for a few months now.

We use a year/month code in our database. It’s not a ‘date format’. It’s actually treated as a text field. The month code for March 2014 would be 201403.

I run the following macro on worksheets that might have many similar pivot tables on a single worksheet. Initially a ‘data’ worksheet is updated with the latest month’s data. Then I go to each sheet containing the pivot tables pointing to the updated data worksheet and run the macro as appropriate. It’s supposed to find each pivot table on the worksheet, add the new month code to the columns and remove the oldest month code on the same run for all the pivot tables on that worksheet. It ‘activates’ the newest month code just fine. However, when I try to hide the oldest month code by using .Visible = False the macro continues to run as if it’s hidden the pivot table item (verified by hovering over the .Visible value while stepping through the code). However, the item remains visible in the actual pivot table.

Here’s the code:

Public Sub UpdateRptMonthInPT_RowLabels()
 
Dim p As Integer
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim FirstMth As String
Dim CurrRptMth As String
Dim i As Integer
 
Question:
CurrRptMth = InputBox("What is the report year/month?" _
                        & vbCr & "(e.g.; year:2012 + month:07 INPUT: 201207)", "Current Report Period?")
If Len(CurrRptMth) <> 6 Then GoTo Question
 
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each pt In ActiveSheet.PivotTables
    pt.RefreshTable
    For Each cell In pt.RowRange
        If cell = "Row Labels" Then
            cell.Select
            ' We want to determine the "oldest" month code
            ActiveCell.Offset(0, 1).Select
            FirstMth = ActiveCell.Value
        End If
    Next
    For Each pf In pt.ColumnFields
        If pf = "Mon" Or pf = "Calendar Yr Mo Cd" Then
            i = pf.PivotItems.Count
            ' Next line activates the newest month code in the pivot table
            pf.PivotItems(i).Visible = True
            ' Since the newest month code is now visible, start the loop with the previous "newest" month and work backwards
            For i = i - 1 To 1 Step -1
                ' We want to remove the "oldest" month code
                If pf.PivotItems(i).Name = FirstMth Then
                    pf.PivotItems(i).Visible = False
                End If
            Next i
        End If
    Next pf
Next pt
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "Done!", vbExclamation
End Sub

I’ve tried looping forwards and backwards through the PivotItems, but the “oldest” month remains visible.

Suggestions are welcome!

 

[dougbert]

After further testing, I've noted:

• The code itself seems to be correct when I step through it.
• If the pf in pt.ColumnFields is named “Mon”, the code always works.
• If the pf in pt.ColumnFields is named “Calendar Yr Mo Cd”, the code works sometimes, but I haven’t established a pattern yet.
• I’ve checked for misspellings or variations in “Calendar Yr Mo Cd”, though I haven’t found any apparent issues with this.
• It appears there is an issue with handling the “Calendar Yr Mo Cd” field name
• I’ll investigate changing the field name to “CalendarYrMoCd”, though that doesn’t seem to be a likely fix on the surface