Hello All,

I am trying to establish a connection between existing slicers and a new pivot table. 

Background: I have slicers on the main dashboard, and  I need to be able to run a macro that creates a report/pivot table based on the slicers chosen from the main dashboard. 

My slicers are named as follows (I need all 4 to connect to the new pivot table): 

-CODE

-Investigator

-Date

-Class

Code written so far: 

Sub Pleasework()

Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long

'Declare Variables
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("FY17-21 Data")

'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

 

'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="FilteredRevenue")

 

'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="FilteredRevenue")

'Insert Row Fields
With ActiveSheet.PivotTables("FilteredRevenue").PivotFields("CODE")
.Orientation = xlRowField
.Position = 1
End With

With ActiveSheet.PivotTables("FilteredRevenue").PivotFields("Investigator ")
.Orientation = xlRowField
.Position = 2
End With

'Insert Data Field
With ActiveSheet.PivotTables("FilteredRevenue").PivotFields("Care Days-Actual ")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "Care Days"
End With

'Insert Data Field
With ActiveSheet.PivotTables("FilteredRevenue").PivotFields("Revenue-Actual ")
.Orientation = xlDataField
.Position = 2
.Function = xlSum
.NumberFormat = "$#,###.00"
.Name = "Revenue"
End With

End Sub

Any help is very appreciated!