Filtering Code

I need code that will do the following.
1.filter each tab based on the selected filter in B1:B5
2.filter each pivot table and its corresponding pivot graph based on the selected criteria
3.if the selection does not apply to the filter the pivot chart should update to 0 or empty on the summary tab
For example if Test1 in Level6, chart P1 on the summary tab should be reduce to nothing. Please see the example

Any Takers?


edit by modus_operandi:
Please refer to replacement file in http:#a35257087
ctownsen80Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
I don't think its possible to have a pivot table selection that returns a null set, so your example may not be possible the way you've structured this sheet.

Which part of what you've set up is NOT working?  E.g., does your first step 1 work or is something broken?  I see you've written code around it.

Finally, are the pivot tables really required?  Would it be more appropriate (given the null set is a possible outcome, based on your example) to use SUMIFS to get at your answers?

We could setup some mini-reports like what you've done with the pivot tables, but leverage the SUMIFS function to generate.  Pie Charts build off that should more adequately reflect the outcome you're looking for.

Let me know what direction you want to head.

Dave
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ctownsen80Author Commented:
Step 3 is not working. If the sumif is the more efficient route, please move forward.
0
dlmilleCommented:
Ok - this was fun, lol.  I spent a while putting in COUNTIFS and SUMIFS then started thinking about the data, the convenience of Pivot Tables, and the fact that keys could change, etc., which we'd need to handle in the SUMIFS/COUNTIFS case and while it WOULD WORK, I took a look again at the problem with #3 while I was running a macro on the initial approach....

I found this could be simplified quite a bit (the code) while maintaining credibility and supportability (as you designed it).  Here's the situation:

1.  If there is a LEVEL that doesn't match in a PivotTable, then we should get the NULL set.  One way to get the NULL set is to set EVERY Level to "(blank)" - how about that?
2.  So, we'd need to set all the Levels to "(blank)" on the no-match condition.
3.  However, we'd need to reset all the Levels once a new change is made.

So the process is this:

1.  First - need to check to see if levels are changing at all on the Summary sheet, otherwise exit the sub - this is handled with this code:

 
Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("B1:B5")) Is Nothing Then 'check to see if levels are changing, otherwise, do nothing
        Exit Sub 'exits if levels not changing
    End If

Open in new window


2.  Then - at the start - let's set all Levels (and nothing else) to "(All)" at the start - note the new variable array strFields() as string.  Also, the levelSelection range variable that we'll be matching against:
 
strFields(1) = strField1
strFields(2) = strField2
strFields(3) = strField3
strFields(4) = strField4
strFields(5) = strField5

Set levelSelection = ThisWorkbook.Sheets("Summary").Range("B1")

On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False

    'first - initialize EVERYTHING to (ALL), second, make changes, Level by Level and if notFound, reset everything to (blank)
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            For Each pf In pt.PageFields
                If InStr(pf.Name, "Level") <> 0 Then 'reset the levels
                    pf.CurrentPage = "(ALL)"
                End If
            Next pf
        Next pt
    Next ws

Open in new window


3.  Finally - we set the levels - for every level - and count the number of times we're successful.  AND if we're NOT, then we do the same routine as the first, except we set ALL levels to "(blank)":

 
'NOW - set the Levels, and if ANY of the levels have no match, set ALL levels to (blank)!
        
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            foundCount = 0
            For i = 1 To UBound(strFields) '1 to the number of levels we care about
                With pt.PageFields(strFields(i))
                    If levelSelection.Offset(i - 1, 0).Value = "All" Then ' then just set it
                        .CurrentPage = "(ALL)"
                        foundCount = foundCount + 1
                    Else
                        For Each pi In .PivotItems
                            If pi.Value = levelSelection.Offset(i - 1, 0).Value Then 'check each level indicator's value
                                .CurrentPage = pi.Value 'so set it on the match
                                If Err.Number = 0 Then
                                    foundCount = foundCount + 1 'increment the found count
                                Else
                                    Err.Clear 'clear for the next check
                                End If
                            End If
                        Next pi
                    End If
                End With
            Next i
            If foundCount < UBound(strFields) Then ' so set them all to blank
                For i = 1 To UBound(strFields)
                    With pt.PageFields(strFields(i))
                        .CurrentPage = "(blank)"
                    End With
                Next i
            End If
        Next pt
    Next ws

Open in new window


Then we reenable events and move forward with the rest of the code....

It seems to test well, for me.  Let me know what you think!

PS - I don't believe you need to turn events off, as you are making no changes in the Summary sheet, so I commented that out.

Here's the entire codepage (I recommend moving almost all of it to an independent MODULE) - and I've done that in the attached:
 
Option Base 1 ' to align array with strField names

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("B1:B5")) Is Nothing Then 'check to see if levels are changing, otherwise, do nothing
        Exit Sub 'exits if levels not changing
    End If
    
Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim pf As Variant
Dim strField1 As String
Dim strField2 As String
Dim strField3 As String
Dim strField4 As String
Dim strField5 As String

Dim strFields(5) As String 'hold all the levels we care about
Dim levelSelection As Range
Dim foundCount As Integer 'number of levels that had a match

Dim L2Filter As String
Dim L3Filter As String
Dim L4Filter As String
Dim L5Filter As String
Dim L6Filter As String

strField1 = "Level2"
strField2 = "Level3"
strField3 = "Level4"
strField4 = "Level5"
strField5 = "Level6"

strFields(1) = strField1
strFields(2) = strField2
strFields(3) = strField3
strFields(4) = strField4
strFields(5) = strField5

Set levelSelection = ThisWorkbook.Sheets("Summary").Range("B1")

On Error Resume Next
'Application.EnableEvents = False
Application.ScreenUpdating = False

    'first - initialize EVERYTHING to (ALL), second, make changes, Level by Level and if notFound, reset everything to (blank)
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            For Each pf In pt.PageFields
                If InStr(pf.Name, "Level") <> 0 Then 'reset the levels
                    pf.CurrentPage = "(ALL)"
                End If
            Next pf
        Next pt
    Next ws
        
    'NOW - set the Levels, and if ANY of the levels have no match, set ALL levels to (blank)!
        
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            foundCount = 0
            For i = 1 To UBound(strFields) '1 to the number of levels we care about
                With pt.PageFields(strFields(i))
                    If levelSelection.Offset(i - 1, 0).Value = "All" Then ' then just set it
                        .CurrentPage = "(ALL)"
                        foundCount = foundCount + 1
                    Else
                        For Each pi In .PivotItems
                            If pi.Value = levelSelection.Offset(i - 1, 0).Value Then 'check each level indicator's value
                                .CurrentPage = pi.Value 'so set it on the match
                                If Err.Number = 0 Then
                                    foundCount = foundCount + 1 'increment the found count
                                Else
                                    Err.Clear 'clear for the next check
                                End If
                            End If
                        Next pi
                    End If
                End With
            Next i
            If foundCount < UBound(strFields) Then ' so set them all to blank
                For i = 1 To UBound(strFields)
                    With pt.PageFields(strFields(i))
                        .CurrentPage = "(blank)"
                    End With
                Next i
            End If
        Next pt
    Next ws
    
'Application.EnableEvents = True
Application.ScreenUpdating = True



L2Filter = Sheet1.Range("b1").Value
L3Filter = Sheet1.Range("b2").Value
L4Filter = Sheet1.Range("b3").Value
L5Filter = Sheet1.Range("b4").Value
L6Filter = Sheet1.Range("b5").Value

If L2Filter = "All" Then
    Sheet2.Range("a1").AutoFilter Field:=2
    Sheet3.Range("a1").AutoFilter Field:=2
    Sheet4.Range("a1").AutoFilter Field:=2
    Sheet5.Range("a1").AutoFilter Field:=2
    Sheet6.Range("a1").AutoFilter Field:=2
    Sheet10.Range("a1").AutoFilter Field:=1
    Sheet11.Range("a1").AutoFilter Field:=2
Else
    Sheet2.Range("a1").AutoFilter Field:=2, Criteria1:=L2Filter
    Sheet3.Range("a1").AutoFilter Field:=2, Criteria1:=L2Filter
    Sheet4.Range("a1").AutoFilter Field:=2, Criteria1:=L2Filter
    Sheet5.Range("a1").AutoFilter Field:=2, Criteria1:=L2Filter
    Sheet6.Range("a1").AutoFilter Field:=2, Criteria1:=L2Filter
    Sheet10.Range("a1").AutoFilter Field:=1, Criteria1:=L2Filter
    Sheet11.Range("a1").AutoFilter Field:=2, Criteria1:=L2Filter
End If
    
If L3Filter = "All" Then
    Sheet2.Range("a1").AutoFilter Field:=3
    Sheet3.Range("a1").AutoFilter Field:=3
    Sheet4.Range("a1").AutoFilter Field:=3
    Sheet5.Range("a1").AutoFilter Field:=3
    Sheet6.Range("a1").AutoFilter Field:=3
    Sheet10.Range("a1").AutoFilter Field:=2
    Sheet11.Range("a1").AutoFilter Field:=3
Else
    Sheet2.Range("a1").AutoFilter Field:=3, Criteria1:=L3Filter
    Sheet3.Range("a1").AutoFilter Field:=3, Criteria1:=L3Filter
    Sheet4.Range("a1").AutoFilter Field:=3, Criteria1:=L3Filter
    Sheet5.Range("a1").AutoFilter Field:=3, Criteria1:=L3Filter
    Sheet6.Range("a1").AutoFilter Field:=3, Criteria1:=L3Filter
    Sheet10.Range("a1").AutoFilter Field:=2, Criteria1:=L3Filter
    Sheet11.Range("a1").AutoFilter Field:=3, Criteria1:=L3Filter
End If

If L4Filter = "All" Then
    Sheet2.Range("a1").AutoFilter Field:=4
    Sheet3.Range("a1").AutoFilter Field:=4
    Sheet4.Range("a1").AutoFilter Field:=4
    Sheet5.Range("a1").AutoFilter Field:=4
    Sheet6.Range("a1").AutoFilter Field:=4
    Sheet10.Range("a1").AutoFilter Field:=3
    Sheet11.Range("a1").AutoFilter Field:=4
Else
    Sheet2.Range("a1").AutoFilter Field:=4, Criteria1:=L4Filter
    Sheet3.Range("a1").AutoFilter Field:=4, Criteria1:=L4Filter
    Sheet4.Range("a1").AutoFilter Field:=4, Criteria1:=L4Filter
    Sheet5.Range("a1").AutoFilter Field:=4, Criteria1:=L4Filter
    Sheet6.Range("a1").AutoFilter Field:=4, Criteria1:=L4Filter
    Sheet10.Range("a1").AutoFilter Field:=3, Criteria1:=L4Filter
    Sheet11.Range("a1").AutoFilter Field:=4, Criteria1:=L4Filter
End If

If L5Filter = "All" Then
    Sheet2.Range("a1").AutoFilter Field:=5
    Sheet3.Range("a1").AutoFilter Field:=5
    Sheet4.Range("a1").AutoFilter Field:=5
    Sheet5.Range("a1").AutoFilter Field:=5
    Sheet6.Range("a1").AutoFilter Field:=5
    Sheet10.Range("a1").AutoFilter Field:=4
    Sheet11.Range("a1").AutoFilter Field:=5
Else
    Sheet2.Range("a1").AutoFilter Field:=5, Criteria1:=L5Filter
    Sheet3.Range("a1").AutoFilter Field:=5, Criteria1:=L5Filter
    Sheet4.Range("a1").AutoFilter Field:=5, Criteria1:=L5Filter
    Sheet5.Range("a1").AutoFilter Field:=5, Criteria1:=L5Filter
    Sheet6.Range("a1").AutoFilter Field:=5, Criteria1:=L5Filter
    Sheet10.Range("a1").AutoFilter Field:=4, Criteria1:=L5Filter
    Sheet11.Range("a1").AutoFilter Field:=5, Criteria1:=L5Filter
End If

If L6Filter = "All" Then
    Sheet2.Range("a1").AutoFilter Field:=6
    Sheet3.Range("a1").AutoFilter Field:=6
    Sheet4.Range("a1").AutoFilter Field:=6
    Sheet5.Range("a1").AutoFilter Field:=6
    Sheet6.Range("a1").AutoFilter Field:=6
    Sheet10.Range("a1").AutoFilter Field:=5
    Sheet11.Range("a1").AutoFilter Field:=6
Else
    Sheet2.Range("a1").AutoFilter Field:=6, Criteria1:=L6Filter
    Sheet3.Range("a1").AutoFilter Field:=6, Criteria1:=L6Filter
    Sheet4.Range("a1").AutoFilter Field:=6, Criteria1:=L6Filter
    Sheet5.Range("a1").AutoFilter Field:=6, Criteria1:=L6Filter
    Sheet6.Range("a1").AutoFilter Field:=6, Criteria1:=L6Filter
    Sheet10.Range("a1").AutoFilter Field:=5, Criteria1:=L6Filter
    Sheet11.Range("a1").AutoFilter Field:=6, Criteria1:=L6Filter
End If

End Sub

Open in new window


Enjoy!

Dave


edit by modus_operandi:
Please refer to replacement file in http:#a35257087
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

dlmilleCommented:
Did you accept this BEFORE I posted my solution??

Sorry - I should have given you a progress indicator, I guess, lol...

What's up?

Dave
0
ctownsen80Author Commented:
I am using your solution...thanks for your help.
0
ctownsen80Author Commented:
Any chance that we can delete the Test files? I omitted to exclude all the data that should have been excluded for this test file.
0
dlmilleCommented:
I'm not sure I understand.  Would you kindly elaborate?

Dave
0
dlmilleCommented:
Oh - I think I know what you mean.  If you can take the final solution and redact any data and submit, I'll get a moderator to delete the files and replace my posted file with your redacted file (I believe I can make that happen).

Dave
0
ctownsen80Author Commented:
Please see the updated test file and update.

Thanks Again!
Test2-r2b.xlsm
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.