Procedure too long - VBA Code

  • Hi, Can someone help me with the code below, I am getting an error for Procedure too long error and I need this code to run :


    Thank you in advance. :)


    P.S The code is a part of a whole thing since cannot post the whole code its more than 10000 lines , Let me know in case of any queries.


    Private Sub CMB_GO_Qtrs_Click()


    If CHK_Q1.Value = True And Chk_Q2.Value = True Then

    Call Qtr_Chk_Q1

    Call Qtr_Chk_Q2

    With ActiveWorkbook.SlicerCaches("Slicer_Assigned_Month1")

    .SlicerItems("Feb").Selected = True

    .SlicerItems("Mar").Selected = True

    .SlicerItems("Apr").Selected = True

    .SlicerItems("May").Selected = True

    .SlicerItems("Jun").Selected = True

    .SlicerItems("Jul").Selected = False

    .SlicerItems("Aug").Selected = False

    .SlicerItems("Sep").Selected = False

    .SlicerItems("Oct").Selected = False

    .SlicerItems("Nov").Selected = False

    .SlicerItems("Dec").Selected = False

    .SlicerItems("(blank)").Selected = False

    .SlicerItems("Jan").Selected = True

    End With

    With ActiveWorkbook.SlicerCaches("Slicer_Assigned_Month")

    .SlicerItems("Feb").Selected = True

    .SlicerItems("Mar").Selected = True

    .SlicerItems("Apr").Selected = True

    .SlicerItems("May").Selected = True

    .SlicerItems("Jun").Selected = True

    .SlicerItems("Jul").Selected = False

    .SlicerItems("Aug").Selected = False

    .SlicerItems("Sep").Selected = False

    .SlicerItems("Oct").Selected = False

    .SlicerItems("Nov").Selected = False

    .SlicerItems("Dec").Selected = False

    .SlicerItems("(blank)").Selected = False

    .SlicerItems("Jan").Selected = True

    End With

    With ActiveWorkbook.SlicerCaches("Slicer_Month")

    .SlicerItems("Feb").Selected = True

    .SlicerItems("Mar").Selected = True

    .SlicerItems("Apr").Selected = True

    .SlicerItems("May").Selected = True

    .SlicerItems("Jun").Selected = True

    .SlicerItems("Jul").Selected = False

    .SlicerItems("Aug").Selected = False

    .SlicerItems("Sep").Selected = False

    .SlicerItems("Oct").Selected = False

    .SlicerItems("Nov").Selected = False

    .SlicerItems("Dec").Selected = False

    .SlicerItems("(blank)").Selected = False

    .SlicerItems("Jan").Selected = True

    End With

    With ActiveWorkbook.SlicerCaches("Slicer_Month1")

    .SlicerItems("Feb").Selected = True

    .SlicerItems("Mar").Selected = True

    .SlicerItems("Apr").Selected = True

    .SlicerItems("May").Selected = True

    .SlicerItems("Jun").Selected = True

    .SlicerItems("Jul").Selected = False

    .SlicerItems("Aug").Selected = False

    .SlicerItems("Sep").Selected = False

    .SlicerItems("Oct").Selected = False

    .SlicerItems("Nov").Selected = False

    .SlicerItems("Dec").Selected = False

    .SlicerItems("(blank)").Selected = False

    .SlicerItems("Jan").Selected = True

    End With

    With ActiveWorkbook.SlicerCaches("Slicer_Month2")

    .SlicerItems("Feb").Selected = True

    .SlicerItems("Mar").Selected = True

    .SlicerItems("Apr").Selected = True

    .SlicerItems("May").Selected = True

    .SlicerItems("Jun").Selected = True

    .SlicerItems("Jul").Selected = False

    .SlicerItems("Aug").Selected = False

    .SlicerItems("Sep").Selected = False

    .SlicerItems("Oct").Selected = False

    .SlicerItems("Nov").Selected = False

    .SlicerItems("Dec").Selected = False

    .SlicerItems("(blank)").Selected = False

    .SlicerItems("Jan").Selected = True

    End With

    With ActiveWorkbook.SlicerCaches("Slicer_Assigned_Month3")

    .SlicerItems("Feb").Selected = True

    .SlicerItems("Mar").Selected = True

    .SlicerItems("Apr").Selected = True

    .SlicerItems("May").Selected = True

    .SlicerItems("Jun").Selected = True

    .SlicerItems("Jul").Selected = False

    .SlicerItems("Aug").Selected = False

    .SlicerItems("Sep").Selected = False

    .SlicerItems("Oct").Selected = False

    .SlicerItems("Nov").Selected = False

    .SlicerItems("Dec").Selected = False

    .SlicerItems("(blank)").Selected = False

    .SlicerItems("Jan").Selected = True

    End With

    With ActiveWorkbook.SlicerCaches("Slicer_Month3")

    .SlicerItems("Jun").Selected = True

    .SlicerItems("Jul").Selected = False

    .SlicerItems("Aug").Selected = False

    .SlicerItems("Sep").Selected = False

    .SlicerItems("Oct").Selected = False

    .SlicerItems("Nov").Selected = False

    .SlicerItems("Dec").Selected = False

    .SlicerItems("Feb").Selected = True

    .SlicerItems("Mar").Selected = True

    .SlicerItems("Apr").Selected = True

    .SlicerItems("May").Selected = True

    .SlicerItems("(blank)").Selected = False

    .SlicerItems("Jan").Selected = True

    End With


    ElseIf CHK_Q1.Value = True And Chk_Q3.Value = True Then

    Call Qtr_Chk_Q1

    Call Qtr_Chk_Q3

    With ActiveWorkbook.SlicerCaches("Slicer_Assigned_Month1")

    .SlicerItems("Feb").Selected = True

    .SlicerItems("Mar").Selected = True

    .SlicerItems("Apr").Selected = False

    .SlicerItems("May").Selected = False

    .SlicerItems("Jun").Selected = False

    .SlicerItems("Jul").Selected = True

    .SlicerItems("Aug").Selected = True

    .SlicerItems("Sep").Selected = True

    .SlicerItems("Oct").Selected = False

    .SlicerItems("Nov").Selected = False

    .SlicerItems("Dec").Selected = False

    .SlicerItems("(blank)").Selected = False

    .SlicerItems("Jan").Selected = True

    End With

    With ActiveWorkbook.SlicerCaches("Slicer_Assigned_Month")

    .SlicerItems("Feb").Selected = True

    .SlicerItems("Mar").Selected = True

    .SlicerItems("Apr").Selected = False

    .SlicerItems("May").Selected = False

    .SlicerItems("Jun").Selected = False

    .SlicerItems("Jul").Selected = True

    .SlicerItems("Aug").Selected = True

    .SlicerItems("Sep").Selected = True

    .SlicerItems("Oct").Selected = False

    .SlicerItems("Nov").Selected = False

    .SlicerItems("Dec").Selected = False

    .SlicerItems("(blank)").Selected = False

    .SlicerItems("Jan").Selected = True

    End With

    With ActiveWorkbook.SlicerCaches("Slicer_Month")

    .SlicerItems("Feb").Selected = True

    .SlicerItems("Mar").Selected = True

    .SlicerItems("Apr").Selected = False

    .SlicerItems("May").Selected = False

    .SlicerItems("Jun").Selected = False

    .SlicerItems("Jul").Selected = True

    .SlicerItems("Aug").Selected = True

    .SlicerItems("Sep").Selected = True

    .SlicerItems("Oct").Selected = False

    .SlicerItems("Nov").Selected = False

    .SlicerItems("Dec").Selected = False

    .SlicerItems("(blank)").Selected = False

    .SlicerItems("Jan").Selected = True

    End With

    With ActiveWorkbook.SlicerCaches("Slicer_Month1")

    .SlicerItems("Feb").Selected = True

    .SlicerItems("Mar").Selected = True

    .SlicerItems("Apr").Selected = False

    .SlicerItems("May").Selected = False

    .SlicerItems("Jun").Selected = False

    .SlicerItems("Jul").Selected = True

    .SlicerItems("Aug").Selected = True

    .SlicerItems("Sep").Selected = True

    .SlicerItems("Oct").Selected = False

    .SlicerItems("Nov").Selected = False

    .SlicerItems("Dec").Selected = False

    .SlicerItems("(blank)").Selected = False

    .SlicerItems("Jan").Selected = True

    End With

    With ActiveWorkbook.SlicerCaches("Slicer_Month2")

    .SlicerItems("Feb").Selected = True

    .SlicerItems("Mar").Selected = True

    .SlicerItems("Apr").Selected = False

    .SlicerItems("May").Selected = False

    .SlicerItems("Jun").Selected = False

    .SlicerItems("Jul").Selected = True

    .SlicerItems("Aug").Selected = True

    .SlicerItems("Sep").Selected = True

    .SlicerItems("Oct").Selected = False

    .SlicerItems("Nov").Selected = False

    .SlicerItems("Dec").Selected = False

    .SlicerItems("(blank)").Selected = False

    .SlicerItems("Jan").Selected = True

    End With

    With ActiveWorkbook.SlicerCaches("Slicer_Assigned_Month3")

    .SlicerItems("Feb").Selected = True

    .SlicerItems("Mar").Selected = True

    .SlicerItems("Apr").Selected = False

    .SlicerItems("May").Selected = False

    .SlicerItems("Jun").Selected = False

    .SlicerItems("Jul").Selected = True

    .SlicerItems("Aug").Selected = True

    .SlicerItems("Sep").Selected = True

    .SlicerItems("Oct").Selected = False

    .SlicerItems("Nov").Selected = False

    .SlicerItems("Dec").Selected = False

    .SlicerItems("(blank)").Selected = False

    .SlicerItems("Jan").Selected = True

    End With

    With ActiveWorkbook.SlicerCaches("Slicer_Month3")

    .SlicerItems("Jun").Selected = False

    .SlicerItems("Jul").Selected = True

    .SlicerItems("Aug").Selected = True

    .SlicerItems("Sep").Selected = True

    .SlicerItems("Oct").Selected = False

    .SlicerItems("Nov").Selected = False

    .SlicerItems("Dec").Selected = False

    .SlicerItems("Feb").Selected = True

    .SlicerItems("Mar").Selected = True

    .SlicerItems("Apr").Selected = False

    .SlicerItems("May").Selected = False

    .SlicerItems("(blank)").Selected = False

    .SlicerItems("Jan").Selected = True

    End With

  • Hi Roy,


    I tried dividing the problem into sub but it's not working as it is a userform where there are 4 Checboxes for each quarter and I want a code to select the quarters according to the need.


    I have made this:


    End Sub

  • For each called part I have designed the Sub procedures before:


    Examle of Q1 is as follows:


  • Shweta,


    There are multiple ways of doing it see example code below, what i have done here is assign values of a year i.e. 12 and loop it into pivot slicers with an if condition.


    you can also use for each option to loop pivot slicers too and then use the below code to loop again into the slicers fields.


  • Hi Ashu,


    Would you be able to explain this code as I have 6 slicer items so shall the same thing like the above code be used for all those 6 slicers?