macro to take values from range to slicers

[aravindhan_3]

Hi,

I have pivot table with sales data by month, and some other pivots as well, I have added city as slicers, may be about 20 cities. if I want to exclude any one or 2 cities, I had to press ctrl and click cities that I don't want.

it is very difficult for me tell my top management to hold ctrl and unselect.

I need a help with macro, where if I enter in another sheet column A, city names that I want to exclude & click a button, the slicers are updated accordingly.


Thanks and regards
Arvind

[p45cal]

Could you do some work for us and provide a file with your set up, with a working pivot etc?
You say 'help with macro'; how far have you got so far?

[aravindhan_3]

sorry for not giving enough information.

attached the file,

Regards
Arvind

[aravindhan_3]

Hi,

tried this code, looks like its working, but taking too much of a time.

any other ways?


Sub Exclude()
With ActiveWorkbook.SlicerCaches("Slicer_Country_Long_Desc")
Dim i As Long
Dim LR As Long
Dim Country_Item As String
LR = Range("L" & Rows.Count).End(xlUp).Row
For i = 6 To LR
Country_Item = Cells(i, 12).Value
.SlicerItems(Country_Item).Selected = False
Next i
End With
Application.Calculate
End Sub

[p45cal]

It doesn't seem to be too bad here, but may be you have lots more pivots and/or a lot more data in the real workbook? How many connected pivots to this slicer? How many rows in the actual database?
It may be a case of adding a bit of code to set each pivot to manual update while adjusting the slicers then resetting to automatic afterwards, along with perhaps switching off screen updating while it's all happening.
I'll have a play around.

Aside from that, will the list of countries to exclude remain fairly static or will the 'managers', who sound astonishingly thick, be changing them fairly often?
I was thinking a list with checkboxes next to each country which a normal mouse-click would toggle on/off? A listbox perhaps?

Also, have you noticed that if only India is showing in the slicer/pivot, when you press the button India doesn't disappear, but needs a second press of the button to make it disappear? I know why it does this and it's easy enough to get around.

[p45cal]

Try in your sample file this macro in a standard module and assigned to your button:
Sub Exclude()
Application.ScreenUpdating = False
On Error GoTo ExitNicely
Set pts = ThisWorkbook.SlicerCaches("Slicer_Country").PivotTables
For Each pt In pts
pt.ManualUpdate = True
Next pt
With ActiveWorkbook.SlicerCaches("Slicer_Country")
Dim i As Long
Dim LR As Long
Dim Country_Item As String
LR = Range("L" & Rows.Count).End(xlUp).Row
For j = 1 To 2
For i = 3 To LR
Country_Item = Cells(i, 12).Value
.SlicerItems(Country_Item).Selected = False
Next i
Next j
End With
For Each pt In pts
pt.ManualUpdate = False
Next pt
ExitNicely:
Application.ScreenUpdating = True
End Sub