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

ThisWorkBook.SlicerCaches(1).Delete
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



Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

One thought on “Automate Slicer Creation Using Table Data”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.