Using VBA to update a pivot filter to multiple values from named range

[AllisonStewart]

Hi All! First, thank you in advance for all your help. I often reference this site for my VBA/excel questions and though this is my first time posting, you all have been a tremendous help to me.

Second, I have to admit, I am a newbie at VBA. Usually, my reporting is adhoc, so I have never really needed the functionality, but I am always trying to grow and improve.

So...here goes, and hopefully I can explain this in text well enough to be understood.

I am reporting cost centre actuals in a pivot table. I have my cost centres grouped into departments (ie, HR, IT, Finance, Design, Sales, etc), and within each group there are multiple cost centres.

I have previously been able to use named ranges to filter my pivot table by individual cost centre, but I want to be able to ALSO have the ability to show all the cost centres within the group.

Below is the code I was previously working with. I was starting on the "Summary" tab and there are two data validation dropdowns. The first is to define the group, and the second is an indirect based off the first to show only the cost centres relating to the group. the second dropdown is named "costcenter"

Sub UpdatePivot()


On Error Resume Next


Application.ScreenUpdating = False
Worksheets("Line Item Pivot").Activate


ActiveSheet.PivotTables("PivotTable1").PivotFields("Cost Center").CurrentPage = CStr(Range("costcenter"))


Worksheets("Summary").Activate


Application.ScreenUpdating = True


End Sub

Is there a way that in the "costcenter" range, to reference another named range with multiple values?

How would I write this?

THANK YOU SO SO MUCH!

Allison

 

[AllisonStewart]

I've been doing a lot of research on this. Is it possible to use a scripting dictionary to load the cost centre values based on the dropdown value, and then filter from there?

Thanks!

 

[Jerry Sullivan]

Hi Allison and welcome to MrExcel!

I understand that you want to use a dynamic range as the list of items to be visible after filtering the pivottable. You kind of lost me with your description of wanting the ability to show all cost centers or just some.

Are you needing to do more than filter the pivot to show the items in Range("costcenter")?

 

[AllisonStewart]

Yes, Jerry. Thank you. I am almost there. Without actually sending the file, I think it might be a little bit of a mystery, but let me insert what I have.

On the Summary tab with the two dropdowns, I have a worksheet change code which when the first is activated, it will run a macro to create a named range, the second will run a macro which will call one of two other macros to run. Everything works EXCEPT the "SinglePivot" macro. I can't figure it out. In fact, I think the "MultiplePivot" is your code, so THANK YOU a million times over.

Sub Worksheet_Change(ByVal Target As Range)


If Not Application.Intersect(Range("c4"), Range(Target.Address)) Is Nothing Then Application.Run "Module1.CCSelection"


If Not Application.Intersect(Range("c8"), Range(Target.Address)) Is Nothing Then Application.Run "Module1.PivotSelect"


End Sub

and the macros below.

Sub CCSelection()


On Error Resume Next


Application.ScreenUpdating = False
Worksheets("Key").Activate
    
    Range("A1").Select
    Cells.Find(What:="STOP", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate
    Range(Selection, Selection.End(xlUp)).Select

'the above code is referencing cells which are using an array formula to index the individual cost centres relating to the first dropdown box ={INDEX($E$5:$E$91, MATCH(0, IF($J$4=$F$5:$F$91, COUNTIF($L$5:$L5, $E$5:$E$91), ""), 0))}.  Actually, it references cells using an if statement.  If formula equals x then y, else STOP.
    
    ActiveWorkbook.Names.Add Name:="CCSelect", RefersTo:=Selection


Worksheets("Summary").Activate
Application.ScreenUpdating = True


End Sub



   Sub PivotSelect()
   
       If Range("C8").Value = "Total CCs" Then
           Call MultiplePivot
       ElseIf Range("C8").Value <> "Total CCs" Then
           Call SinglePivot
       End If
   End Sub


Sub MultiplePivot()


On Error Resume Next


Application.ScreenUpdating = False
Worksheets("Line Item Pivot").Activate


    Dim PI As PivotItem
    Dim vRNG


    vRNG = Application.Transpose(Range("CCSelect"))
        
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Cost Center")
    .ClearAllFilters
    For Each PI In .PivotItems
        If Len(Join(Filter(vRNG, PI, True, vbBinaryCompare))) = 0 Then
            PI.Visible = False
        End If
    Next PI
     
    End With
    
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Cost Center")
        .PivotItems("").Visible = False
    End With
    
Worksheets("Summary").Activate
Application.ScreenUpdating = True


End Sub


Sub SinglePivot()


On Error Resume Next


Application.ScreenUpdating = False
Worksheets("Line Item Pivot").Activate


Dim pt As PivotTable
Set pt = Sheets("Line Item Pivot").PivotTables("PivotTable1")
 
pt.PivotFields("Cost Center").ClearAllFilters
pt.PivotFields("Cost Center").CurrentPage = Range("SingleCC")
    
Worksheets("Summary").Activate
Application.ScreenUpdating = True


End Sub

Thanks again for all your help

 

[AllisonStewart]

SOLVED!!!!!!!!

Ok, so I really don't know if my way is the BEST way to do this, but mission accomplished, nonetheless.

All the code is the same as the previous post with the exception of the "SinglePivot" sub. Below.

Sub SinglePivot()


On Error Resume Next


Application.ScreenUpdating = False
Worksheets("Line Item Pivot").Activate


With ActiveSheet.PivotTables("PivotTable1").PivotFields("Cost Center")
    .ClearAllFilters
    .PivotFilters.Add Type:=xlCaptionEquals, Value1:=Range("SingleCC").Value
End With


Worksheets("Summary").Activate
Application.ScreenUpdating = True


End Sub

 

[dkmanley]

Hi Jerry,


This is a 3rd post worded slightly differently for the same problem I am having. It’s a problem similar to this thread. I am trying to create a macro in VBA that will use a dynamic range on sheet “Criteria” to filter a pivot table “Pivottable1” on sheet “FCC”, pivot item (pi) “License Sku”.


The dynamic range = OFFSET(Criteria!$D$3,0,0,COUNTA(Criteria!$D:$D)-1,COUNTA(Criteria!$1:$1))
A user can paste values starting at cell D3, and then run macro.


I found one of your solutions and modified it. It runs but seems to get caught in a loop. I should point out that there are 163K (pi)’s in the “License Sku” list.


Your code:
Sub Filter_ItemListInRange()
Filter_PivotField _
pvtField:=Sheets("FCC").PivotTables("PivotTable1").PivotFields("License Sku"), _
varItemList:=Application.Transpose(Sheets("Criteria").Range("=OFFSET(Criteria!$D$3,0,0,COUNTA(Criteria!$D:$D)-1,COUNTA(Criteria!$1:$1))"))
End Sub
Function Filter_PivotField(pvtField As PivotField, _
varItemList As Variant)
Dim strItem1 As String
Dim i As Long
On Error GoTo ErrorHandler:
Application.ScreenUpdating = False

strItem1 = varItemList(LBound(varItemList))
With pvtField
.PivotItems(strItem1).Visible = True
For i = 1 To .PivotItems.Count
If .PivotItems(i) <> strItem1 And _
.PivotItems(i).Visible = True Then
.PivotItems(i).Visible = False
End If
Next i
For i = LBound(varItemList) + 1 To UBound(varItemList)
.PivotItems(varItemList(i)).Visible = True
Next i
End With
Exit Function
ErrorHandler:
MsgBox "Error while trying to process item: " & varItemList(i)
End Function


Thanks in advance, dkmanley