Create Slicer For Pivot Table with Excel VBA

To create a Slicer for a Pivot Table using VBA, you can follow this step-by-step guide with a detailed explanation. The process involves creating a Pivot Table, adding data, and using VBA to insert a Slicer connected to the Pivot Table. Here’s the code and explanation:

Detailed VBA Code to Create a Slicer for a Pivot Table

Sub CreatePivotTableWithSlicer()
    Dim ws As Worksheet
    Dim pivotCache As PivotCache
    Dim pivotTable As PivotTable
    Dim pivotRange As Range
    Dim slicer As SlicerCache
    ' Step 1: Set up worksheet and data range
    Set ws = ThisWorkbook.Sheets("Sheet1")  ' Change Sheet1 to your desired sheet name   
    ' Set the range of data that the Pivot Table will be based on
    Set pivotRange = ws.Range("A1:D100")  ' Change this to your actual data range
    ' Step 2: Create a Pivot Cache based on the data range
    Set pivotCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=pivotRange)
    ' Step 3: Create a new Pivot Table on a new worksheet
    Set pivotTable = ws.PivotTables.Add(PivotCache:=pivotCache, TableDestination:=ws.Range("F1"))
    ' Step 4: Set up the Pivot Table fields
    With pivotTable
        ' Add fields to the Pivot Table: "Column1", "Column2", etc. should be actual column headers in your data
        .PivotFields("Category").Orientation = xlRowField  ' Replace "Category" with your row field name
        .PivotFields("Amount").Orientation = xlDataField  ' Replace "Amount" with your data field name
        .PivotFields("Region").Orientation = xlColumnField  ' Replace "Region" with your column field name
    End With
    ' Step 5: Create a Slicer and connect it to the Pivot Table
    Set slicer = ThisWorkbook.SlicerCaches.Add(pivotTable, "Category")  ' "Category" is the field for the slicer 
    ' Insert the Slicer on the worksheet
    slicer.CreateSlicer SlicerDestination:=ws.Range("J1")
    ' Optional: Adjust Slicer formatting if needed
    With slicer.Slicers(1)
        .Shape.Width = 200
        .Shape.Height = 200
        .Top = 100
        .Left = 300
    End With
End Sub

Explanation of Each Step:

1. Set up Worksheet and Data Range:
   • We first define the worksheet (ws) where the Pivot Table will be created.
   • The pivotRange represents the range of data that will be used for the Pivot Table. Adjust this to match your actual data range in Excel.
2. Create a Pivot Cache:
   • A Pivot Cache is an internal Excel object that stores the source data for a Pivot Table. It is created using the PivotCaches.Create method, specifying the data range (pivotRange) as the source.
3. Create a Pivot Table:
   • We create a new Pivot Table using the PivotTables.Add method. The Pivot Table is placed at a specific destination range (TableDestination:=ws.Range(« F1 »)), which is in cell F1 of the ws worksheet.
4. Set up Pivot Table Fields:
   • After creating the Pivot Table, we define the fields that will populate the Pivot Table. In this example:
     • Category is added to the Row field.
     • Amount is added to the Data field.
     • Region is added to the Column field.
   • Replace these field names with your actual data field names.
5. Create and Add Slicer:
   • We add a Slicer connected to the Pivot Table using the SlicerCaches.Add method, specifying the field for which the Slicer will be created (in this case, Category).
   • The Slicer is inserted into the worksheet with the CreateSlicer method, and the position can be adjusted with the Range argument.
6. Optional: Adjust Slicer Formatting:
   • You can adjust the size and position of the Slicer using the Shape.Width, Shape.Height, Top, and Left properties.

Additional Notes:

• Ensure that your data contains the columns (fields) you want to use in the Pivot Table and Slicer.
• The Slicer can be linked to multiple Pivot Tables if they share the same field.
• Modify the Range and field names based on your actual data and layout.

This code will automate the creation of a Pivot Table and the associated Slicer in Excel using VBA.