SOLVED

Pivot Field Gets Hidden After Refresh

Contributor

Hello,

 

I've got an Excel template file with pretty basic refresh and clear table macros, and stuff. One specific pivot table's one specific field gets hidden after the source data is removed,new data is entered and pivot's refreshed. I have to enable the field again manually. This pivot table uses Excel Data Model. Data source uses some formulas. This specific field's value is just numbers and it doesn't contain any formulas. The field is in the value area. I'm using Excel Data Model because the default model doesn't display time values correctly and I'm having lots of issues with getpivotdata function. I created another pivot with the same data source, deleted source data except for the table headers then refreshed and the field got deactivated again.

 

I've uploaded the file I'm working on. The specific pivot is in the Pivot Hourly Queue sheet.

3 Replies
best response confirmed by kheldar (Contributor)
Solution

@kheldar I tried looking at the file and really don't understand what you are trying to do and / or what is broken. Your macros could be simplified by having 1 macro be "ClearRows(s, t)" and then just pass those parameters for each of the cases so you call the same macro 3x instead of 3 different macros that are identical otherwise. But that doesn't address what you are asking.  The 1 thing i can add is that I have had issues when updating data and refreshing having the table missing data.  I found by removing a field, hitting refresh all and then adding that field back in and then refresh all again sometimes helps.  I also wrote a macro that basically does a refresh and tried to find it.  I think this is it:

Function ResetPivotTable(Optional PT As PivotTable)
    On Error Resume Next
    If IsMissing(PT) Or IsError(PT.Name = "") Then
      If (IsError(Selection.PivotTable)) Then
SetPT:  Set PT = ActiveSheet.PivotTables(1)
      Else
        Set PT = Selection.PivotTable
      End If
    End If
    On Error GoTo 0
    Dim pf() 'array of page fields (filter fields)
    Dim pfp() 'array of current page field settings
    If PT.PageFields.Count <= 0 Then
        ResetPivotTable = PT.RefreshTable()
    Else
        i = PT.PageFields.Count
        ReDim pf(1 To i)
        ReDim pfp(1 To i)
        j = 0
        For Each p In PT.PageFields
            j = j + 1
            Set pf(j) = p
            pfp(j) = p.CurrentPage
            p.Orientation = xlHidden
        Next p
        
        If (j <> i) Or Not (PT.RefreshTable()) Then
            Call MsgBox("Error resetting the pivot table", , "Error Resetting Pivot Table")
            ResetPivotTable = False
            Exit Function
        End If
        For j = i To 1 Step -1
            Call PT.AddFields(, , Array(pf(j)), True)
            PT.PageFields(pf(j).Value).CurrentPage = pfp(j)
        Next j
        
    End If
    
End Function
Wow!! Your suggestion worked at the second try. Well I'm pretty new to VBA. I couldn't get the same macro work for different tables on different sheets Maybe you could help me do that? It'd optimize things.
It actually already is designed to do that by allowing a parameter to be passed to tell it which pivot table to update:
Function ResetPivotTable(Optional PT As PivotTable)

if you don't pass it anything it just performs the reset on the first pivot table on the active sheet.
If you run ResetPivotTable(aDifferentPivotTable) then it will execute on aDifferentPivotTable instead.