PDA

View Full Version : VBA Code Help Needed



rahulroyal12
03-10-2020, 03:53 PM
Hello all,

I am new to Excel VBA and working on a project to help speed up an OLAP CUBE Dashboard with the help of VBA. I have 6 master slicers which are connected to 13 sheets with pivot tables and pivot charts. For now, I have been able to remove the slicers connections from all the sheets. I am looking to select specific selections on the slicers and connect them to all the sheets. I wanted to have a code snippet to be able to do that.

I want to be able to do the above mentioned. I would be grateful if somebody helped me with the issue.

'Disconnecting Slicers 2
Sub DisconnectPTToSlicers()
Dim SlicersDict As Variant
Dim PTDict As Variant
Set SlicersDict = CreateObject("Scripting.Dictionary")
Dim sl As SlicerCache, slpt As PivotTable, SlItem As Variant, pt As Variant, I As Byte
Dim start_time
Dim end_time
start_time = Now()
'create a dictionary of dictionaries with slicers and connected pivot tables
For Each sl In ThisWorkbook.SlicerCaches
Set PTDict = CreateObject("Scripting.Dictionary")
For Each slpt In sl.PivotTables
PTDict.Add Key:=slpt.Parent.Name & slpt.Name, Item:=slpt
Next
SlicersDict.Add Key:=sl.Name, Item:=PTDict
Next

For Each SlItem In SlicersDict.Keys
'remove pt connections for this slicer
Set PTDict = SlicersDict(SlItem)
pt = PTDict.items
If UBound(pt) >= LBound(pt) Then
For I = LBound(pt) To UBound(pt)
ThisWorkbook.SlicerCaches(SlItem).PivotTables.RemovePivotTable (pt(I))
Next
End If
Next




Set SlicersDict = Nothing


Set PTDict = Nothing


end_time = Now()
MsgBox (DateDiff("s", start_time, end_time))
End Sub






'Active Sheet Connect


Sub Active_Pivot_Sheet_Connect()
Dim start_time
Dim end_time
start_time = Now()
For Each Pivot In ActiveSheet.PivotTables
Pivot.ManualUpdate = True
For Each sl In ThisWorkbook.SlicerCaches
If sl.VisibleSlicerItems.Count >= 0 Then
sl.PivotTables.AddPivotTable (Pivot)
End If
Next sl
Pivot.ManualUpdate = False
Next Pivot
end_time = Now()
MsgBox (DateDiff("s", start_time, end_time))
End Sub

This is the code I have written. However, I want to have select particular slicer selections and connect them to all the pivot tables to filter for those values.

Thank You,

Rahul