March 17, 2019

Automate Slicer Creation Using Table Data

How to automate slicer creation using table data with VBA. We can use slicers to filter data. Table data or Pivot Table data can be filtered with the buttons provided by slicers. Slicers provide a way not only to filter data but also helps the user understand the present filtering state and what data is filtered.

When a user selects an item from the slicer, the data for this item is displayed in the Excel worksheet range in the form of a report. For example, if you select Judy Carter from the ‘Sales Person’ column, her data is displayed and you can see her sales performance. You can also display multiple ‘sales persons’ data by selecting multiple items from the ‘sales person’ field. Watch the video below to learn how the complete process of slicer creation can be automated using data in a table:

Automate Slicer Creation Using Table Data

Watch this video on YouYube.

Sub CreateTable()
ThisWorkbook.Worksheets(“Sheet1”).ListObjects.Add(xlSrcRange, [A1].CurrentRegion, , xlYes).Name = “myTable”

End Sub

Sub CreateSlicer()

Dim rng As Range
Dim sl As Slicer
Dim sc As SlicerCache
Dim ws As Worksheet

Set ws = Worksheets(“Sheet1”)
On Error Resume Next

On Error GoTo 0
Set sc = ThisWorkbook.SlicerCaches.Add2(Sheet1.ListObjects(“myTable”), “Sales Person”)

Set sl = sc.Slicers.Add(ws, , “sales Person”, “Select Sales Person”)
Set rng = Range(“A1:E1”)
sl.Top = rng.Top
sl.Left = rng.Left + rng.Width + 40
sl.Width = 150 ‘there are 72 points to an inch or 28.35 points to a centimeter
sl.Height = 120

End Sub

One thought on “Automate Slicer Creation Using Table Data

Comments are closed.