For this example, I build a little macro that should run slowly — it checks 100K rows of data, and enters a value in the adjacent cell. Cells(l Row End, l Col)) End With For Each c In rng Sel str Group = "" Select Case c.
I’m sure there are ways to make this run faster, but for this exercise we’ll just focus on how to show a message in the status bar. Value Case Is As I said earlier, it’s easy to add a few messages in the status bar, so you can keep an eye on the macro’s progress.
The source data has four cities, and we only want Boston and Los Angeles available in the slicer The slicer shows all the items, and we can't hide the cities that we don't want to use. Name) Set si Short = Nothing On Error Resume Next Set si Short = sc Short. Name) On Error Go To err Handler If Not si Short Is Nothing Then If si Short. If you check the slicer on the short list sheet, it should have changed, to show the same city (or cities) selected. The zipped file contains macros, so enable macros to test the slicers.
Instead, we'll create another pivot table, with just the short list of cities, and use programming to change the selections in the main slicer.
To help you stay informed, you can show messages in the status bar, to let you know what progress is being made.
File System Object") 'change folder path of excel files here Set dir Obj = merge Obj. Note: All the solutions given here are published after thoroughly tested to my knowledge, however it is advised to keep a copy before you try it.When you select an item in one slicer, it will automatically select the same item in another slicer.If multiple pivot tables are based on the same data source, you can create a slicer, and connect it to one or more of those pivot tables. Selected = False End If Next si Long exit Handler: Application. Enable Events = True Exit Sub err Handler: Msg Box "Could not update pivot table" Resume exit Handler End Sub The final step is to move the slicers -- the short list slicer will go onto the main sheet, beside the original pivot table.Here is the macro, before adding the status bar messages. It’s done in one block of code, so there aren’t any sections where a simple message could be shown.It doesn’t really take too long to run, but should give you time to see the status bar messages changing, when we add those. So, for this macro, I added a row number check, at the beginning of the “For each c…” loop In the code, the Mod function shows the remainder after the row number is divided by 10000.