Automatic Slicer Selection VBA

user_17730966 0
Viewed 85

I am trying to automate my dashboard as efficient as possible. For that i need my slicer to automatically select this month and the previous month.

Currently it manually updates by a different macro for every month, which deselects everything except the months in scope.

Manual Code.png

Doing it this way gives the dashboard performance issues. It doesn't matter if it works with formulas or VBA.

Are there any solutions for this?

slicers dashboard vba excel
1 Answers

Use Month() and DateAdd() to determine the current and previous months.

Sub MTDSelectThisMonthAndPreviousMonth()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents -False
    With ActiveWorkbook.SlicerCaches("Datenschnitt__Month__Int___Invoice_Date1")
        .Slicerltems("1").Selected = False
        .Slicerltems("2").Selected = False
        .Slicerltems("3").Selected = False
        .Slicerltems("4").Selected = False
        .Slicerltems("5").Selected = False
        .Slicerltems("6").Selected = False
        .Slicerltems("7").Selected = False
        .Slicerltems("8").Selected = False
        .Slicerltems("9").Selected = False
        .Slicerltems("10").Selected = False
        .Slicerltems(" 11 ").Selected = False
        .Slicerltems("12").Selected = False
        .Slicerltems(Month(Date)).Selected = True
        .Slicerltems(Month(DateAdd("m", -1, Date))).Selected = True
    End With
End Sub
user_99127140
Related