Multiple VBA and Slicers

ndox123

New Member
Joined
Aug 1, 2019
Messages
6
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....

JANFEBMARAPRMAYJUNQ1Q2FY 2016JANFEBMARAPRMAYJUNQ1Q2FY 2017
AU
AU
NZ
SG
SG
UK
UK
US
US

<tbody>
</tbody>

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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top