If you add a couple of dynamic ranges:
"codes": =OFFSET(Pivot!$E$1,1,0,COUNTA(Pivot!$E:$E)-1,2)
"pivotdata": =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),4)
then change the data source of your to be pivotdata, and alter your code to
when you add new entries to the Data tab or the list of codes on the Pivot tab and click the button it will refresh everything.![]()
Sub Hide_Pivot_items() Dim pt As PivotTable Dim i As Integer Set pt = ActiveSheet.PivotTables(1) Application.ScreenUpdating = False Application.DisplayAlerts = False pt.PivotCache.Refresh With pt.PivotFields("Code") For i = 1 To [codes].Rows.Count .PivotItems(i).Visible = [codes].Resize(1, 1).Offset(i - 1, 1).Value Next End With Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub
Col
Bookmarks