vba - Pivot Report Filter - if only one value avaliable select else ALL -

requesting working on macro based pivot table have 5 columns in report filter. looking vba code these 5 filer, should show value if filter contains single value else should remain (all).

currently using following code pivot filer:

    sub insertpivottable()  'declare variables dim psheet worksheet dim dsheet worksheet dim pcache pivotcache dim ptable pivottable dim prange range dim lastrow long dim lastcol long  'delete preivous pivot table worksheet & insert new blank worksheet same name on error resume next application.displayalerts = false worksheets("pivot").delete sheets.add before:=activesheet activesheet.name = "pivot" application.displayalerts = true set psheet = worksheets("pivot") set dsheet = worksheets("report")   'define data range lastrow = dsheet.cells(rows.count, 1).end(xlup).row lastcol = dsheet.cells(1, columns.count).end(xltoleft).column set prange = dsheet.cells(1, 1).resize(lastrow, lastcol)  'define pivot cache set pcache = activeworkbook.pivotcaches.create _ (sourcetype:=xldatabase, sourcedata:=prange). _ createpivottable(tabledestination:=psheet.cells(1, 1), _ tablename:="adt_pivottable")  'insert blank pivot table set ptable = pcache.createpivottable _ (tabledestination:=psheet.cells(1, 1), tablename:="adt_pivottable")  'insert reportfilter fields    activesheet.pivottables("adt_pivottable").pivotfields("resp bus partn id")         .orientation = xlpagefield         .position = 1     end     activesheet.pivottables("adt_pivottable").pivotfields("adt-file id")         .orientation = xlpagefield         .position = 1     end     activesheet.pivottables("adt_pivottable").pivotfields("uwy")         .orientation = xlpagefield         .position = 1     end     activesheet.pivottables("adt_pivottable").pivotfields("scob - acc")         .orientation = xlpagefield         .position = 1     end     activesheet.pivottables("adt_pivottable").pivotfields("curr")         .orientation = xlpagefield         .position = 1     end end sub 

the answer , code little long, i've added few "bonuses" you, you'll find useful :)

first, there no need delete "pivot" sheet , re-create update pivottable named "adt_pivottable", can update pivotcache updated sourcedata, , afterwards refresh pivottable updated pivotcache.

second, i’ve added second sub, check every pivotfield passed how many pivotitems has, if there’s 1 pivotitem display in filter, otherwise show "all".

sub insertpivottable code

option explicit  sub insertpivottable()  'declare variables dim psheet worksheet dim dsheet worksheet dim pcache pivotcache dim ptable pivottable dim prange range dim lastrow long dim lastcol long  'delete preivous pivot table worksheet & insert new blank worksheet same name on error resume next application.displayalerts = false  set psheet = worksheets("pivot") on error goto 0 if psheet nothing ' if "pivot" sheet doesn't exist     set psheet = sheets.add(before:=activesheet)     psheet.name = "pivot" end if application.displayalerts = true  set dsheet = worksheets("report") 'define data range dsheet     lastrow = .cells(.rows.count, 1).end(xlup).row     lastcol = .cells(1, .columns.count).end(xltoleft).column     set prange = .range("a1").resize(lastrow, lastcol) ' set data range pivot table end  ' set pivot cache set pcache = activeworkbook.pivotcaches.create(sourcetype:=xldatabase, sourcedata:=prange)  on error resume next set ptable = psheet.pivottables("adt_pivottable") ' set pivot table if exists previous code runs  on error goto 0 if ptable nothing ' <-- pivot table still doesn't exist >> create first time     ' create new pivot table in "pivot" sheet     set ptable = psheet.pivottables.add(pivotcache:=pcache, tabledestination:=psheet.range("a1"), tablename:="adt_pivottable")      ptable         'insert reportfilter fields         .pivotfields("resp bus partn id")             .orientation = xlpagefield             .position = 1         end         .pivotfields("adt-file id")             .orientation = xlpagefield             .position = 1         end         .pivotfields("uwy")             .orientation = xlpagefield             .position = 1         end         .pivotfields("scob - acc")             .orientation = xlpagefield             .position = 1         end         .pivotfields("curr")             .orientation = xlpagefield             .position = 1         end     end else     ' refresh pivot cache updated range     ptable.changepivotcache pcache     ptable.refreshtable end if  ' modify filter default view each of pivot fields below ptable     selectfiltersinglevalue .pivotfields("resp bus partn id")     selectfiltersinglevalue .pivotfields("adt-file id")     selectfiltersinglevalue .pivotfields("uwy")     selectfiltersinglevalue .pivotfields("scob - acc")     selectfiltersinglevalue .pivotfields("curr") end  end sub 

sub selectfiltersinglevalue code

sub selectfiltersinglevalue(ptfld pivotfield)  dim ptitm       pivotitem dim count       long  ptfld     .enablemultiplepageitems = true      each ptitm in .pivotitems         if ptitm.name <> "(blank)"             ptitm.visible = true             count = count + 1         else             ptitm.visible = false         end if     next ptitm      if count > 1         .clearallfilters     end if end  end sub