Multiple VBA and Slicers

[ndox123]

Hi mrExcel!

I'm currently working on a report whereby I'm trying to create multiple macros in one worksheet with some slicers inserted. I have one slicer working with the below macro. It consists of the period names, i.e. Jan, Feb,Mar, Q1, Q2, FY. So if I click on "Feb" it'll only show me all the Februaries for every year and hide the other columns. However, I need another slicer where it has the Countries, e.g. AU, NZ, SG. And I need to have the same event happened when I select one country, the rest of the other countries (rows) are hidden. And if possible I need the two slicers to work together as well, that is if I click Australia and Q1, it'll show me all Australia stores and amount in Q1 from all the years.

I'm hoping I explain myself well enough! apologies if it's confusing....

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]MAR[/TD]
[TD]APR[/TD]
[TD]MAY[/TD]
[TD]JUN[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]FY 2016[/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]MAR[/TD]
[TD]APR[/TD]
[TD]MAY[/TD]
[TD]JUN[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]FY 2017[/TD]
[/TR]
[TR]
[TD]AU
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AU[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NZ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SG[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SG[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

PivotTableUpdate


Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)


Select Case Target.Name
Case "PivotTable6"
Call m_FilterCol.Filter_Columns("rngPeriodName", "Sales by Events", "Sales by Events", "PivotTable6", "Period Name")
End Select

End Sub



Module:

Sub Filter_Columns(sHeaderRange As String, _
sReportSheet As String, _
sPivotSheet As String, _
sPivotName As String, _
sPivotField As String _
)


Dim c As Range
Dim rCol As Range
Dim pi As PivotItem


'Unhide all columns
Worksheets(sReportSheet).Range(sHeaderRange).EntireColumn.Hidden = False

'Loop through each cell in the header range and compare to the selected filter item(s).
'Hide columns that are not selected/filtered out.

For Each c In Worksheets(sReportSheet).Range(sHeaderRange).Cells

'Check if the pivotitem exists
With Worksheets(sPivotSheet).PivotTables(sPivotName).PivotFields(sPivotField)
On Error Resume Next
Set pi = .PivotItems(c.Value)
On Error GoTo 0
End With

'If the pivotitem exists then check if it is visible (filtered)
If Not pi Is Nothing Then
If pi.Visible = False Then

'Add excluded items to the range to be hidden
If rCol Is Nothing Then
Set rCol = c
Else
Set rCol = Union(rCol, c)
End If
End If
End If

'Reset the pivotitem
Set pi = Nothing

Next c

'Hide the columns of the range of excluded pivot items
If Not rCol Is Nothing Then
rCol.EntireColumn.Hidden = True
End If

End Sub