This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.
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
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
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
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
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