How to ignore PivotItems that don't exist when changing PivotField in vba

SamP1983 · Aug 4, 2015

Hello,

I created a dashboard where the user needs to paste raw data in a sheet. Afterwards the user can choose between different types of reports and choose the months that will be shown in these reports, therefore the user needs to select a start month and end month in the dashboard. Depending on the months selected, data is retrieved from several pivot tables. However I'm struggling with the following part in VBA.

In the raw data there is a column "Month" which is used as a filter in PivotTable5. Depending on the selected months by the user I need to unselect PivotItems in the PivotTables. Let's say that the user has uploaded raw data from January to August and chooses January as start month and August as end month in the dashboard, through VBA I would need to unselect the other months of the year (September to December). To do this I used the code below:

PHP:

    If sd.List(sd.ListIndex) = "January" And ed.List(ed.ListIndex) = "August" Then
    Sheets("Pivots").PivotTables("PivotTable5").PivotFields("Month").PivotItems("September").Visible = False
    Sheets("Pivots").PivotTables("PivotTable5").PivotFields("Month").PivotItems("October").Visible = False
    Sheets("Pivots").PivotTables("PivotTable5").PivotFields("Month").PivotItems("November").Visible = False
    Sheets("Pivots").PivotTables("PivotTable5").PivotFields("Month").PivotItems("December").Visible = False

The problem is that I will get an error message saying these PivotItems cannot be found.

Does anyone know how I can write a code that will work?

Thank you in advance!

Sam

Jerry Sullivan · Aug 4, 2015

Hi Sam,

There's a couple of ways to do handle PivotItems that don't exist. The simplest is just to place the code that might throw an error inside an On Error Resume Next error handler.

Code:

 On Error Resume Next
 Sheets("Pivots").PivotTables("PivotTable5").PivotFields("Month").PivotItems("September").Visible = False
 '.....
 '.....
 On Error GoTo 0

Does your code include If Then blocks for each combination of Months? That could probably be made more concise using variables for the start and end months.

If you want some help with that, please post your entire procedure.

SamP1983 · Aug 5, 2015

Hi Jerry,

Thanks for your feedback! This helps a lot!

My code does indeed include If Then blocks for each combination of months. Would you mind taking a look at it and see if some of the parts can be made shorter and more efficient? I'm sure a lot of parts of my code can be done a lot more professional and efficiently, I'm looking forward to learn from your input.

Code:

Private Sub CommandButton1_Click()

    If CheckBox1.Value = False And CheckBox2.Value = False And CheckBox3.Value = False And CheckBox4.Value = False Then
    Sheets("Booked Services").Visible = False
    Sheets("Turnover per Entity").Visible = False
    Sheets("Air Report").Visible = False
    Sheets("Air Report (Online)").Visible = False
    UserForm4.Show
    Call CommandButton2_Click
    
    Else

    Application.ScreenUpdating = False

    UserForm1.Show False
    
    DoEvents
    
    Call UnprotectAll
    
    Dim ReportTitle As String
    Dim StartMonth As String
    Dim EndMonth As String
    Dim Year As String
    Dim ddstart As DropDown
    Dim ddend As DropDown
    
    Set ddstart = ActiveSheet.Shapes("Vervolgkeuzelijst 29").OLEFormat.Object
    Set ddend = ActiveSheet.Shapes("Vervolgkeuzelijst 28").OLEFormat.Object
    ReportTitle = TextBox1.Text
    Year = 2015
    
    With Sheets("Booked Services")
        
        .Shapes("Tekstvak 15").TextFrame.Characters.Text = ReportTitle & " (" & ddstart.List(ddstart.ListIndex) & " - " & ddend.List(ddend.ListIndex) & " " & Year & ")"
        .Shapes("Tekstvak 16").TextFrame.Characters.Text = ReportTitle & " (" & ddstart.List(ddstart.ListIndex) & " - " & ddend.List(ddend.ListIndex) & " " & Year & ")"
    
    End With
    
    With Sheets("Turnover per Entity")
        
        .Shapes("Tekstvak 11").TextFrame.Characters.Text = ReportTitle & " (" & ddstart.List(ddstart.ListIndex) & " - " & ddend.List(ddend.ListIndex) & " " & Year & ")"
    
    End With

    With Sheets("Air Report")
        
        .Shapes("Tekstvak 10").TextFrame.Characters.Text = ReportTitle & " (" & ddstart.List(ddstart.ListIndex) & " - " & ddend.List(ddend.ListIndex) & " " & Year & ")"
        .Shapes("Tekstvak 12").TextFrame.Characters.Text = ReportTitle & " (" & ddstart.List(ddstart.ListIndex) & " - " & ddend.List(ddend.ListIndex) & " " & Year & ")"
        .Shapes("Tekstvak 14").TextFrame.Characters.Text = ReportTitle & " (" & ddstart.List(ddstart.ListIndex) & " - " & ddend.List(ddend.ListIndex) & " " & Year & ")"
        .Shapes("Tekstvak 16").TextFrame.Characters.Text = ReportTitle & " (" & ddstart.List(ddstart.ListIndex) & " - " & ddend.List(ddend.ListIndex) & " " & Year & ")"
        .Shapes("Tekstvak 18").TextFrame.Characters.Text = ReportTitle & " (" & ddstart.List(ddstart.ListIndex) & " - " & ddend.List(ddend.ListIndex) & " " & Year & ")"
    
    End With
    
    Dim ws As Worksheet
    Dim sd As DropDown
    Dim ed As DropDown
    Dim JanJan As Range, JanFeb As Range, JanMar As Range, JanApr As Range, JanMay As Range, JanJun As Range, JanJul As Range, JanAug As Range, JanSep As Range, JanOct As Range, JanNov As Range, febfeb As Range, febmar As Range, febapr As Range, febmay As Range, febjun As Range, febjul As Range, febaug As Range, febsep As Range, feboct As Range, febnov As Range, febdec As Range, marmar As Range, marapr As Range, marmay As Range, marjun As Range, marjul As Range, maraug As Range, marsep As Range, maroct As Range, marnov As Range, mardec As Range, aprapr As Range, aprmay As Range, aprjun As Range, aprjul As Range, apraug As Range, aprsep As Range, aproct As Range, aprnov As Range, aprdec As Range, maymay As Range, mayjun As Range, mayjul As Range, mayaug As Range, maysep As Range, mayoct As Range, maynov As Range, maydec As Range, junjun As Range, junjul As Range, junaug As Range, junsep As Range, junoct As Range, junnov As Range, jundec As Range, juljul As Range, julaug As Range, julsep As Range, _
    juloct As Range, julnov As Range, juldec As Range, sepsep As Range, sepoct As Range, sepnov As Range, sepdec As Range, octoct As Range, octnov As Range, octdec As Range, novnov As Range, novdec As Range, decdec As Range
    
    Set ws = ActiveSheet
    Set sd = ws.Shapes("Vervolgkeuzelijst 29").OLEFormat.Object
    Set ed = ws.Shapes("Vervolgkeuzelijst 28").OLEFormat.Object
    Set JanJan = Sheets("Booked Services").Range("D6:N13,D44:N51")
    Set JanFeb = Sheets("Booked Services").Range("E6:N13,E44:N51")
    Set JanMar = Sheets("Booked Services").Range("F6:N13,F44:N51")
    Set janmar2 = Sheets("Booked Services").Range("D15:F22,D53:F60")
    Set JanApr = Sheets("Booked Services").Range("G6:N13,G44:N51")
    Set JanMay = Sheets("Booked Services").Range("H6:N13,H44:N51")
    Set JanJun = Sheets("Booked Services").Range("I6:N13,I44:N51")
    Set janjun2 = Sheets("Booked Services").Range("E15:F22,E53:F60")
    Set JanJul = Sheets("Booked Services").Range("J6:N13,J44:N51")
    Set JanAug = Sheets("Booked Services").Range("K6:N13,K44:N51")
    Set JanSep = Sheets("Booked Services").Range("L6:N13,L44:N51")
    Set jansep2 = Sheets("Booked Services").Range("F15:F22,F53:F60")
    Set JanOct = Sheets("Booked Services").Range("M6:N13,M44:N51")
    Set JanNov = Sheets("Booked Services").Range("N6:N13,N44:N51")
    
    Set febfeb = Sheets("Booked Services").Range("C6:C13,E6:N13,C44:C51,E44:N51")
    Set febmar = Sheets("Booked Services").Range("C6:C13,F6:N13,C44:C51,F44:N51")
    Set febapr = Sheets("Booked Services").Range("C6:C13,G6:N13,C44:C51,G44:N51")
    Set febmay = Sheets("Booked Services").Range("C6:C13,H6:N13,C44:C51,H44:N51")
    Set febjun = Sheets("Booked Services").Range("C6:C13,I6:N13,C44:C51,I44:N51")
    Set febjul = Sheets("Booked Services").Range("C6:C13,J6:N13,C44:C51,J44:N51")
    Set febaug = Sheets("Booked Services").Range("C6:C13,K6:N13,C44:C51,K44:N51")
    Set febsep = Sheets("Booked Services").Range("C6:C13,L6:N13,C44:C51,L44:N51")
    Set feboct = Sheets("Booked Services").Range("C6:C13,M6:N13,C44:C51,M44:N51")
    Set febnov = Sheets("Booked Services").Range("C6:C13,N6:N13,C44:C51,N44:N51")
    Set febdec = Sheets("Booked Services").Range("C6:C13,C44:C51")
    
    Set marmar = Sheets("Booked Services").Range("C6:D13,F6:N13,C44:D51,F44:N51")
    Set marapr = Sheets("Booked Services").Range("C6:D13,G6:N13,C44:D51,G44:N51")
    Set marmay = Sheets("Booked Services").Range("C6:D13,H6:N13,C44:D51,H44:N51")
    Set marjun = Sheets("Booked Services").Range("C6:D13,I6:N13,C44:D51,I44:N51")
    Set marjul = Sheets("Booked Services").Range("C6:D13,J6:N13,C44:D51,J44:N51")
    Set maraug = Sheets("Booked Services").Range("C6:D13,K6:N13,C44:D51,K44:N51")
    Set marsep = Sheets("Booked Services").Range("C6:D13,L6:N13,C44:D51,L44:N51")
    Set maroct = Sheets("Booked Services").Range("C6:D13,M6:N13,C44:D51,M44:N51")
    Set marnov = Sheets("Booked Services").Range("C6:D13,N6:N13,C44:D51,N44:N51")
    Set mardec = Sheets("Booked Services").Range("C6:D13,C44:D51")
    
    Set aprapr = Sheets("Booked Services").Range("C6:E13,G6:N13,C44:E51,G44:N51")
    Set aprmay = Sheets("Booked Services").Range("C6:E13,H6:N13,C44:E51,H44:N51")
    Set aprjun = Sheets("Booked Services").Range("C6:E13,I6:N13,C44:E51,I44:N51")
    Set aprjun2 = Sheets("Booked Services").Range("C15:C22,E15:F22,C53:C60,E53:F60")
    Set aprjul = Sheets("Booked Services").Range("C6:E13,J6:N13,C44:E51,J44:N51")
    Set apraug = Sheets("Booked Services").Range("C6:E13,K6:N13,C44:E51,K44:N51")
    Set aprsep = Sheets("Booked Services").Range("C6:E13,L6:N13,C44:E51,L44:N51")
    Set aprsep2 = Sheets("Booked Services").Range("C53:C60,F53:F60")
    Set aproct = Sheets("Booked Services").Range("C6:E13,M6:N13,C44:E51,M44:N51")
    Set aprnov = Sheets("Booked Services").Range("C6:E13,N6:N13,C44:E51,N44:N51")
    Set aprdec = Sheets("Booked Services").Range("C6:E13,C44:E51")
    Set aprdec2 = Sheets("Booked Services").Range("C53:C60")
    
    Set maymay = Sheets("Booked Services").Range("C6:F13,H6:N13,C44:F51,H44:N51")
    Set mayjun = Sheets("Booked Services").Range("C6:F13,I6:N13,C44:F51,I44:N51")
    Set mayjul = Sheets("Booked Services").Range("C6:F13,J6:N13,C44:F51,J44:N51")
    Set mayaug = Sheets("Booked Services").Range("C6:F13,K6:N13,C44:F51,K44:N51")
    Set maysep = Sheets("Booked Services").Range("C6:F13,L6:N13,C44:F51,L44:N51")
    Set mayoct = Sheets("Booked Services").Range("C6:F13,M6:N13,C44:F51,M44:N51")
    Set maynov = Sheets("Booked Services").Range("C6:F13,N6:N13,C44:F51,N44:N51")
    Set maydec = Sheets("Booked Services").Range("C6:F13,C44:F51")
    
    Set junjun = Sheets("Booked Services").Range("C6:G13,I6:N13,C44:G51,I44:N51")
    Set junjul = Sheets("Booked Services").Range("C6:G13,J6:N13,C44:G51,J44:N51")
    Set junaug = Sheets("Booked Services").Range("C6:G13,K6:N13,C44:G51,K44:N51")
    Set junsep = Sheets("Booked Services").Range("C6:G13,L6:N13,C44:G51,L44:N51")
    Set junoct = Sheets("Booked Services").Range("C6:G13,M6:N13,C44:G51,M44:N51")
    Set junnov = Sheets("Booked Services").Range("C6:G13,N6:N13,C44:G51,N44:N51")
    Set jundec = Sheets("Booked Services").Range("C6:G13,C44:G51")
    
    Set juljul = Sheets("Booked Services").Range("C6:H13,J6:N13,C44:H51,J44:N51")
    Set julaug = Sheets("Booked Services").Range("C6:H13,K6:N13,C44:H51,K44:N51")
    Set julsep = Sheets("Booked Services").Range("C6:H13,L6:N13,C44:H51,L44:N51")
    Set julsep2 = Sheets("Booked Services").Range("C53:D60,F53:D60")
    Set juloct = Sheets("Booked Services").Range("C6:H13,M6:N13,C44:H51,M44:N51")
    Set julnov = Sheets("Booked Services").Range("C6:H13,N6:N13,C44:H51,N44:N51")
    Set juldec = Sheets("Booked Services").Range("C6:H13,C44:H51")
    Set juldec2 = Sheets("Booked Services").Range("C53:D60")
    
    Set augaug = Sheets("Booked Services").Range("C6:I13,K6:N13,C44:I51,K44:N51")
    Set augsep = Sheets("Booked Services").Range("C6:I13,L6:N13,C44:I51,L44:N51")
    Set augoct = Sheets("Booked Services").Range("C6:I13,M6:N13,C44:I51,M44:N51")
    Set augnov = Sheets("Booked Services").Range("C6:I13,N6:N13,C44:I51,N44:N51")
    Set augdec = Sheets("Booked Services").Range("C6:I13,C44:I51")
    
    Set sepsep = Sheets("Booked Services").Range("C6:J13,L6:N13,C44:J51,L44:N51")
    Set sepoct = Sheets("Booked Services").Range("C6:J13,M6:N13,C44:J51,M44:N51")
    Set sepnov = Sheets("Booked Services").Range("C6:J13,N6:N13,C44:J51,N44:N51")
    Set sepdec = Sheets("Booked Services").Range("C6:J13,C44:J51")
    
    Set octoct = Sheets("Booked Services").Range("C6:K13,M6:N13,C44:K51,M44:N51")
    Set octnov = Sheets("Booked Services").Range("C6:K13,N6:N13,C44:K51,N44:N51")
    Set octdec = Sheets("Booked Services").Range("C6:K13,C44:K51")
    Set octdec2 = Sheets("Booked Services").Range("C53:E60")
    
    Set novnov = Sheets("Booked Services").Range("C6:L13,N6:N13,C44:L51,N44:N51")
    Set novdec = Sheets("Booked Services").Range("C6:L13,C44:L51")
    
    Set decdec = Sheets("Booked Services").Range("C6:M13,C44:M51")
    
    Set janjanB = Sheets("Turnover per Entity").Range("D6:N47")
    Set janfebB = Sheets("Turnover per Entity").Range("E6:N47")
    Set janmarB = Sheets("Turnover per Entity").Range("F6:N47")
    Set janmar2B = Sheets("Turnover per Entity").Range("D49:F90")
    Set janaprB = Sheets("Turnover per Entity").Range("G6:N47")
    Set janmayB = Sheets("Turnover per Entity").Range("H6:N47")
    Set janjunB = Sheets("Turnover per Entity").Range("I6:N47")
    Set janjun2B = Sheets("Turnover per Entity").Range("E49:F90")
    Set janjulB = Sheets("Turnover per Entity").Range("J6:N47")
    Set janaugB = Sheets("Turnover per Entity").Range("K6:N47")
    Set jansepB = Sheets("Turnover per Entity").Range("L6:N47")
    Set jansep2B = Sheets("Turnover per Entity").Range("F49:F90")
    Set janoctB = Sheets("Turnover per Entity").Range("M6:N47")
    Set jannovB = Sheets("Turnover per Entity").Range("N6:N47")
    
    Set febfebB = Sheets("Turnover per Entity").Range("C6:C47,E6:N47")
    Set febmarB = Sheets("Turnover per Entity").Range("C6:C47,F6:N47")
    Set febaprB = Sheets("Turnover per Entity").Range("C6:C47,G6:N47")
    Set febmayB = Sheets("Turnover per Entity").Range("C6:C47,H6:N47")
    Set febjunB = Sheets("Turnover per Entity").Range("C6:C47,I6:N47")
    Set febjulB = Sheets("Turnover per Entity").Range("C6:C47,J6:N47")
    Set febaugB = Sheets("Turnover per Entity").Range("C6:C47,K6:N47")
    Set febsepB = Sheets("Turnover per Entity").Range("C6:C47,L6:N47")
    Set feboctB = Sheets("Turnover per Entity").Range("C6:C47,M6:N47")
    Set febnovB = Sheets("Turnover per Entity").Range("C6:C47,N6:N47")
    Set febdecB = Sheets("Turnover per Entity").Range("C6:C47")
    
    Set marmarB = Sheets("Turnover per Entity").Range("C6:D47,F6:N47")
    Set maraprB = Sheets("Turnover per Entity").Range("C6:D47,G6:N47")
    Set marmayB = Sheets("Turnover per Entity").Range("C6:D47,H6:N47")
    Set marjunB = Sheets("Turnover per Entity").Range("C6:D47,I6:N47")
    Set marjulB = Sheets("Turnover per Entity").Range("C6:D47,J6:N47")
    Set maraugB = Sheets("Turnover per Entity").Range("C6:D47,K6:N47")
    Set marsepB = Sheets("Turnover per Entity").Range("C6:D47,L6:N47")
    Set maroctB = Sheets("Turnover per Entity").Range("C6:D47,M6:N47")
    Set marnovB = Sheets("Turnover per Entity").Range("C6:D47,N6:N47")
    Set mardecB = Sheets("Turnover per Entity").Range("C6:D47")
    
    Set apraprB = Sheets("Turnover per Entity").Range("C6:E47,G6:N47")
    Set aprmayB = Sheets("Turnover per Entity").Range("C6:E47,H6:N47")
    Set aprjunB = Sheets("Turnover per Entity").Range("C6:E47,I6:N47")
    Set aprjun2B = Sheets("Booked Services").Range("C49:C90,E49:F90")
    Set aprjulB = Sheets("Turnover per Entity").Range("C6:E47,J6:N47")
    Set apraugB = Sheets("Turnover per Entity").Range("C6:E47,K6:N47")
    Set aprsepB = Sheets("Turnover per Entity").Range("C6:E47,L6:N47")
    Set aprsep2B = Sheets("Booked Services").Range("C49:C90,F49:F90")
    Set aproctB = Sheets("Turnover per Entity").Range("C6:E47,M6:N47")
    Set aprnovB = Sheets("Turnover per Entity").Range("C6:E47,N6:N47")
    Set aprdecB = Sheets("Turnover per Entity").Range("C6:E47")
    Set aprdec2B = Sheets("Booked Services").Range("C49:C90")
    
    Set maymayB = Sheets("Turnover per Entity").Range("C6:F47,H6:N47")
    Set mayjunB = Sheets("Turnover per Entity").Range("C6:F47,I6:N47")
    Set mayjulB = Sheets("Turnover per Entity").Range("C6:F47,J6:N47")
    Set mayaugB = Sheets("Turnover per Entity").Range("C6:F47,K6:N47")
    Set maysepB = Sheets("Turnover per Entity").Range("C6:F47,L6:N47")
    Set mayoctB = Sheets("Turnover per Entity").Range("C6:F47,M6:N47")
    Set maynovB = Sheets("Turnover per Entity").Range("C6:F47,N6:N47")
    Set maydecB = Sheets("Turnover per Entity").Range("C6:F47")
    
    Set junjunB = Sheets("Turnover per Entity").Range("C6:G47,I6:N47")
    Set junjulB = Sheets("Turnover per Entity").Range("C6:G47,J6:N47")
    Set junaugB = Sheets("Turnover per Entity").Range("C6:G47,K6:N47")
    Set junsepB = Sheets("Turnover per Entity").Range("C6:G47,L6:N47")
    Set junoctB = Sheets("Turnover per Entity").Range("C6:G47,M6:N47")
    Set junnovB = Sheets("Turnover per Entity").Range("C6:G47,N6:N47")
    Set jundecB = Sheets("Turnover per Entity").Range("C6:G47")
    
    Set juljulB = Sheets("Turnover per Entity").Range("C6:H47,J6:N47")
    Set julaugB = Sheets("Turnover per Entity").Range("C6:H47,K6:N47")
    Set julsepB = Sheets("Turnover per Entity").Range("C6:H47,L6:N47")
    Set julsep2B = Sheets("Booked Services").Range("C49:D90,F49:F90")
    Set juloctB = Sheets("Turnover per Entity").Range("C6:H47,M6:N47")
    Set julnovB = Sheets("Turnover per Entity").Range("C6:H47,N6:N47")
    Set juldecB = Sheets("Turnover per Entity").Range("C6:H47")
    Set juldec2B = Sheets("Booked Services").Range("C49:D90")
    
    Set augaugB = Sheets("Turnover per Entity").Range("C6:I47,K6:N47")
    Set augsepB = Sheets("Turnover per Entity").Range("C6:I47,L6:N47")
    Set augoctB = Sheets("Turnover per Entity").Range("C6:I47,M6:N47")
    Set augnovB = Sheets("Turnover per Entity").Range("C6:I47,N6:N47")
    Set augdecB = Sheets("Turnover per Entity").Range("C6:I47")
    
    Set sepsepB = Sheets("Turnover per Entity").Range("C6:J47,L6:N47")
    Set sepoctB = Sheets("Turnover per Entity").Range("C6:J47,M6:N47")
    Set sepnovB = Sheets("Turnover per Entity").Range("C6:J47,N6:N47")
    Set sepdecB = Sheets("Turnover per Entity").Range("C6:J47")
    
    Set octoctB = Sheets("Turnover per Entity").Range("C6:K47,M6:N47")
    Set octnovB = Sheets("Turnover per Entity").Range("C6:K47,N6:N47")
    Set octdecB = Sheets("Turnover per Entity").Range("C6:K47")
    Set octdec2B = Sheets("Booked Services").Range("C49:E90")
    
    Set novnovB = Sheets("Turnover per Entity").Range("C6:L47,N6:N47")
    Set novdecB = Sheets("Turnover per Entity").Range("C6:L47")
    
    Set decdecB = Sheets("Turnover per Entity").Range("C6:M47")

    Set janjanC = Sheets("Air Report").Range("D90:N92,D137:N139")
    Set janfebC = Sheets("Air Report").Range("E90:N92,E137:N139")
    Set janmarC = Sheets("Air Report").Range("F90:N92,F137:N139")
    Set janaprC = Sheets("Air Report").Range("G90:N92,G137:N139")
    Set janmayC = Sheets("Air Report").Range("H90:N92,H137:N139")
    Set janjunC = Sheets("Air Report").Range("I90:N92,I137:N139")
    Set janjulC = Sheets("Air Report").Range("J90:N92,J137:N139")
    Set janaugC = Sheets("Air Report").Range("K90:N92,K137:N139")
    Set jansepC = Sheets("Air Report").Range("L90:N92,L137:N139")
    Set janoctC = Sheets("Air Report").Range("M90:N92,M137:N139")
    Set jannovC = Sheets("Air Report").Range("N90:N92,N137:N139")
    
    Set febfebC = Sheets("Air Report").Range("C90:C92,E90:N92,C137:C139,E137:N139")
    Set febmarC = Sheets("Air Report").Range("C90:C92,F90:N92,C137:C139,F137:N139")
    Set febaprC = Sheets("Air Report").Range("C90:C92,G90:N92,C137:C139,G137:N139")
    Set febmayC = Sheets("Air Report").Range("C90:C92,H90:N92,C137:C139,H137:N139")
    Set febjunC = Sheets("Air Report").Range("C90:C92,I90:N92,C137:C139,I137:N139")
    Set febjulC = Sheets("Air Report").Range("C90:C92,J90:N92,C137:C139,J137:N139")
    Set febaugC = Sheets("Air Report").Range("C90:C92,K90:N92,C137:C139,K137:N139")
    Set febsepC = Sheets("Air Report").Range("C90:C92,L90:N92,C137:C139,L137:N139")
    Set feboctC = Sheets("Air Report").Range("C90:C92,M90:N92,C137:C139,M137:N139")
    Set febnovC = Sheets("Air Report").Range("C90:C92,N90:N92,C137:C139,N137:N139")
    Set febdecC = Sheets("Air Report").Range("C90:C92,C137:C139")
    
    Set marmarC = Sheets("Air Report").Range("C90:D92,F90:N92,C137:D139,F137:N139")
    Set maraprC = Sheets("Air Report").Range("C90:D92,G90:N92,C137:D139,G137:N139")
    Set marmayC = Sheets("Air Report").Range("C90:D92,H90:N92,C137:D139,H137:N139")
    Set marjunC = Sheets("Air Report").Range("C90:D92,I90:N92,C137:D139,I137:N139")
    Set marjulC = Sheets("Air Report").Range("C90:D92,J90:N92,C137:D139,J137:N139")
    Set maraugC = Sheets("Air Report").Range("C90:D92,K90:N92,C137:D139,K137:N139")
    Set marsepC = Sheets("Air Report").Range("C90:D92,L90:N92,C137:D139,L137:N139")
    Set maroctC = Sheets("Air Report").Range("C90:D92,M90:N92,C137:D139,M137:N139")
    Set marnovC = Sheets("Air Report").Range("C90:D92,N90:N92,C137:D139,N137:N139")
    Set mardecC = Sheets("Air Report").Range("C90:D92,C137:D139")
    
    Set apraprC = Sheets("Air Report").Range("C90:E92,G90:N92,C137:E139,G137:N139")
    Set aprmayC = Sheets("Air Report").Range("C90:E92,H90:N92,C137:E139,H137:N139")
    Set aprjunC = Sheets("Air Report").Range("C90:E92,I90:N92,C137:E139,I137:N139")
    Set aprjulC = Sheets("Air Report").Range("C90:E92,J90:N92,C137:E139,J137:N139")
    Set apraugC = Sheets("Air Report").Range("C90:E92,K90:N92,C137:E139,K137:N139")
    Set aprsepC = Sheets("Air Report").Range("C90:E92,L90:N92,C137:E139,L137:N139")
    Set aproctC = Sheets("Air Report").Range("C90:E92,M90:N92,C137:E139,M137:N139")
    Set aprnovC = Sheets("Air Report").Range("C90:E92,N90:N92,C137:E139,N137:N139")
    Set aprdecC = Sheets("Air Report").Range("C90:E92,C137:E139")
    
    Set maymayC = Sheets("Air Report").Range("C90:F92,H90:N92,C137:F139,H137:N139")
    Set mayjunC = Sheets("Air Report").Range("C90:F92,I90:N92,C137:F139,I137:N139")
    Set mayjulC = Sheets("Air Report").Range("C90:F92,J90:N92,C137:F139,J137:N139")
    Set mayaugC = Sheets("Air Report").Range("C90:F92,K90:N92,C137:F139,K137:N139")
    Set maysepC = Sheets("Air Report").Range("C90:F92,L90:N92,C137:F139,L137:N139")
    Set mayoctC = Sheets("Air Report").Range("C90:F92,M90:N92,C137:F139,M137:N139")
    Set maynovC = Sheets("Air Report").Range("C90:F92,N90:N92,C137:F139,N137:N139")
    Set maydecC = Sheets("Air Report").Range("C90:F92,C137:F139")
    
    Set junjunC = Sheets("Air Report").Range("C90:G92,I90:N92,C137:G139,I137:N139")
    Set junjulC = Sheets("Air Report").Range("C90:G92,J90:N92,C137:G139,J137:N139")
    Set junaugC = Sheets("Air Report").Range("C90:G92,K90:N92,C137:G139,K137:N139")
    Set junsepC = Sheets("Air Report").Range("C90:G92,L90:N92,C137:G139,L137:N139")
    Set junoctC = Sheets("Air Report").Range("C90:G92,M90:N92,C137:G139,M137:N139")
    Set junnovC = Sheets("Air Report").Range("C90:G92,N90:N92,C137:G139,N137:N139")
    Set jundecC = Sheets("Air Report").Range("C90:G92,C137:G139")
    
    Set juljulC = Sheets("Air Report").Range("C90:H92,J90:N92,C137:H139,J137:N139")
    Set julaugC = Sheets("Air Report").Range("C90:H92,K90:N92,C137:H139,K137:N139")
    Set julsepC = Sheets("Air Report").Range("C90:H92,L90:N92,C137:H139,L137:N139")
    Set juloctC = Sheets("Air Report").Range("C90:H92,M90:N92,C137:H139,M137:N139")
    Set julnovC = Sheets("Air Report").Range("C90:H92,N90:N92,C137:H139,N137:N139")
    Set juldecC = Sheets("Air Report").Range("C90:H92,C137:H139")
    
    Set augaugC = Sheets("Air Report").Range("C90:I92,K90:N92,C137:I139,K137:N139")
    Set augsepC = Sheets("Air Report").Range("C90:I92,L90:N92,C137:I139,L137:N139")
    Set augoctC = Sheets("Air Report").Range("C90:I92,M90:N92,C137:I139,M137:N139")
    Set augnovC = Sheets("Air Report").Range("C90:I92,N90:N92,C137:I139,N137:N139")
    Set augdecC = Sheets("Air Report").Range("C90:I92,C137:I139")
    
    Set sepsepC = Sheets("Air Report").Range("C90:J92,L90:N92,C137:J139,L137:N139")
    Set sepoctC = Sheets("Air Report").Range("C90:J92,M90:N92,C137:J139,M137:N139")
    Set sepnovC = Sheets("Air Report").Range("C90:J92,N90:N92,C137:J139,N137:N139")
    Set sepdecC = Sheets("Air Report").Range("C90:J92,C137:J139")
    
    Set octoctC = Sheets("Air Report").Range("C90:K92,M90:N92,C137:K139,M137:N139")
    Set octnovC = Sheets("Air Report").Range("C90:K92,N90:N92,C137:K139,N137:N139")
    Set octdecC = Sheets("Air Report").Range("C90:K92,C137:K139")
    
    Set novnovC = Sheets("Air Report").Range("C90:L92,N90:N92,C137:L139,N137:N139")
    Set novdecC = Sheets("Air Report").Range("C90:L92,C137:L139")
    
    Set decdecC = Sheets("Air Report").Range("C90:M92,C137:M139")
    
    Sheets("Booked Services").Visible = True
    Sheets("Turnover per Entity").Visible = True
    Sheets("Air Report").Visible = True
    Sheets("Air Report (Online)").Visible = True
    
    Sheets("Pivots").PivotTables("Draaitabel5").PivotFields("Month").ClearAllFilters
    Sheets("Pivots").PivotTables("Draaitabel6").PivotFields("Month").ClearAllFilters
    Sheets("Pivots").PivotTables("Draaitabel7").PivotFields("Month").ClearAllFilters
    Sheets("Pivots").PivotTables("Draaitabel8").PivotFields("Month").ClearAllFilters
    Sheets("Pivots").PivotTables("Draaitabel14").PivotFields("Month").ClearAllFilters
    Sheets("Pivots").PivotTables("Draaitabel1").PivotFields("Month").ClearAllFilters
    Sheets("Pivots").PivotTables("Draaitabel15").PivotFields("Month").ClearAllFilters
    
    If sd.List(sd.ListIndex) = "January" And ed.List(ed.ListIndex) = "January" Then
    JanJan.Delete Shift:=xlToLeft
    janjanB.Delete Shift:=xlToLeft
    janjanC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Application.Run "JanJanx"
    Call Report1UpdatesWithoutQuarter
    
    ElseIf sd.List(sd.ListIndex) = "January" And ed.List(ed.ListIndex) = "February" Then
    JanFeb.Delete Shift:=xlToLeft
    janfebB.Delete Shift:=xlToLeft
    janfebC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Application.Run "JanFebx"
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "January" And ed.List(ed.ListIndex) = "March" Then
    JanMar.Delete Shift:=xlToLeft
    janmar2.Delete Shift:=xlToLeft
    janmarB.Delete Shift:=xlToLeft
    janmar2B.Delete Shift:=xlToLeft
    janmarC.Delete Shift:=xlToLeft
    Application.Run "JanMarx"
    Call Report1UpdatesWithQuarter

    ElseIf sd.List(sd.ListIndex) = "January" And ed.List(ed.ListIndex) = "April" Then
    JanApr.Delete Shift:=xlToLeft
    janaprB.Delete Shift:=xlToLeft
    janaprC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Application.Run "JanAprx"
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "January" And ed.List(ed.ListIndex) = "May" Then
    JanMay.Delete Shift:=xlToLeft
    janmayB.Delete Shift:=xlToLeft
    janmayC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Application.Run "JanMayx"
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "January" And ed.List(ed.ListIndex) = "June" Then
    JanJun.Delete Shift:=xlToLeft
    janjun2.Delete Shift:=xlToLeft
    janjunB.Delete Shift:=xlToLeft
    janjun2B.Delete Shift:=xlToLeft
    janjunC.Delete Shift:=xlToLeft
    Application.Run "JanJunx"
    Call Report1UpdatesWithQuarter

    ElseIf sd.List(sd.ListIndex) = "January" And ed.List(ed.ListIndex) = "July" Then
    JanJul.Delete Shift:=xlToLeft
    janjulB.Delete Shift:=xlToLeft
    janjulC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    Application.Run "JanJulx"
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "January" And ed.List(ed.ListIndex) = "August" Then
    JanAug.Delete Shift:=xlToLeft
    janaugB.Delete Shift:=xlToLeft
    janaugC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Application.Run "JanAugx"
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "January" And ed.List(ed.ListIndex) = "September" Then
    JanSep.Delete Shift:=xlToLeft
    jansep2.Delete Shift:=xlToLeft
    jansepB.Delete Shift:=xlToLeft
    jansep2B.Delete Shift:=xlToLeft
    jansepC.Delete Shift:=xlToLeft
    Application.Run "JanSepx"
    Call Report1UpdatesWithQuarter

    ElseIf sd.List(sd.ListIndex) = "January" And ed.List(ed.ListIndex) = "October" Then
    JanOct.Delete Shift:=xlToLeft
    janoctB.Delete Shift:=xlToLeft
    janoctC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Application.Run "JanOctx"
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "January" And ed.List(ed.ListIndex) = "November" Then
    JanNov.Delete Shift:=xlToLeft
    jannovB.Delete Shift:=xlToLeft
    jannovC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Application.Run "JanNovx"
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "February" And ed.List(ed.ListIndex) = "February" Then
    febfeb.Delete Shift:=xlToLeft
    febfebB.Delete Shift:=xlToLeft
    febfebC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "February" And ed.List(ed.ListIndex) = "March" Then
    febmar.Delete Shift:=xlToLeft
    febmarB.Delete Shift:=xlToLeft
    febmarC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "February" And ed.List(ed.ListIndex) = "April" Then
    febapr.Delete Shift:=xlToLeft
    febaprB.Delete Shift:=xlToLeft
    febaprC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "February" And ed.List(ed.ListIndex) = "May" Then
    febmay.Delete Shift:=xlToLeft
    febmayB.Delete Shift:=xlToLeft
    febmayC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "February" And ed.List(ed.ListIndex) = "June" Then
    febjun.Delete Shift:=xlToLeft
    febjunB.Delete Shift:=xlToLeft
    febjunC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "February" And ed.List(ed.ListIndex) = "July" Then
    febjul.Delete Shift:=xlToLeft
    febjulB.Delete Shift:=xlToLeft
    febjulC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "February" And ed.List(ed.ListIndex) = "August" Then
    febaug.Delete Shift:=xlToLeft
    febaugB.Delete Shift:=xlToLeft
    febaugC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "February" And ed.List(ed.ListIndex) = "September" Then
    febsep.Delete Shift:=xlToLeft
    febsepB.Delete Shift:=xlToLeft
    febsepC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "February" And ed.List(ed.ListIndex) = "October" Then
    feboct.Delete Shift:=xlToLeft
    feboctB.Delete Shift:=xlToLeft
    feboctC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "February" And ed.List(ed.ListIndex) = "November" Then
    febnov.Delete Shift:=xlToLeft
    febnovB.Delete Shift:=xlToLeft
    febnovC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "February" And ed.List(ed.ListIndex) = "December" Then
    febdec.Delete Shift:=xlToLeft
    febdecB.Delete Shift:=xlToLeft
    febdecC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "March" And ed.List(ed.ListIndex) = "March" Then
    marmar.Delete Shift:=xlToLeft
    marmarB.Delete Shift:=xlToLeft
    marmarC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "March" And ed.List(ed.ListIndex) = "April" Then
    marapr.Delete Shift:=xlToLeft
    maraprB.Delete Shift:=xlToLeft
    maraprC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "March" And ed.List(ed.ListIndex) = "May" Then
    marmay.Delete Shift:=xlToLeft
    marmayB.Delete Shift:=xlToLeft
    marmayC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "March" And ed.List(ed.ListIndex) = "June" Then
    marjun.Delete Shift:=xlToLeft
    marjunB.Delete Shift:=xlToLeft
    marjunC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "March" And ed.List(ed.ListIndex) = "July" Then
    marjul.Delete Shift:=xlToLeft
    marjulB.Delete Shift:=xlToLeft
    marjulC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "March" And ed.List(ed.ListIndex) = "August" Then
    maraug.Delete Shift:=xlToLeft
    maraugB.Delete Shift:=xlToLeft
    maraugC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "March" And ed.List(ed.ListIndex) = "September" Then
    marsep.Delete Shift:=xlToLeft
    marsepB.Delete Shift:=xlToLeft
    marsepC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "March" And ed.List(ed.ListIndex) = "October" Then
    maroct.Delete Shift:=xlToLeft
    maroctB.Delete Shift:=xlToLeft
    maroctC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "March" And ed.List(ed.ListIndex) = "November" Then
    marnov.Delete Shift:=xlToLeft
    marnovB.Delete Shift:=xlToLeft
    marnovC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "March" And ed.List(ed.ListIndex) = "December" Then
    mardec.Delete Shift:=xlToLeft
    mardecB.Delete Shift:=xlToLeft
    mardecC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "April" And ed.List(ed.ListIndex) = "April" Then
    aprapr.Delete Shift:=xlToLeft
    apraprB.Delete Shift:=xlToLeft
    apraprC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "April" And ed.List(ed.ListIndex) = "May" Then
    aprmay.Delete Shift:=xlToLeft
    aprmayB.Delete Shift:=xlToLeft
    aprmayC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "April" And ed.List(ed.ListIndex) = "June" Then
    aprjun.Delete Shift:=xlToLeft
    aprjun2.Delete Shift:=xlToLeft
    aprjunB.Delete Shift:=xlToLeft
    aprjun2B.Delete Shift:=xlToLeft
    aprjunC.Delete Shift:=xlToLeft
    Call Report1UpdatesWithQuarter

    ElseIf sd.List(sd.ListIndex) = "April" And ed.List(ed.ListIndex) = "July" Then
    aprjul.Delete Shift:=xlToLeft
    aprjulB.Delete Shift:=xlToLeft
    aprjulC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "April" And ed.List(ed.ListIndex) = "August" Then
    apraug.Delete Shift:=xlToLeft
    apraugB.Delete Shift:=xlToLeft
    apraugC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "April" And ed.List(ed.ListIndex) = "September" Then
    aprsep.Delete Shift:=xlToLeft
    aprsep2.Delete Shift:=xlToLeft
    aprsepB.Delete Shift:=xlToLeft
    aprsep2B.Delete Shift:=xlToLeft
    aprsepC.Delete Shift:=xlToLeft
    Call Report1UpdatesWithQuarter

    ElseIf sd.List(sd.ListIndex) = "April" And ed.List(ed.ListIndex) = "October" Then
    aproct.Delete Shift:=xlToLeft
    aproctB.Delete Shift:=xlToLeft
    aproctC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "April" And ed.List(ed.ListIndex) = "November" Then
    aprnov.Delete Shift:=xlToLeft
    aprnovB.Delete Shift:=xlToLeft
    aprnovC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "April" And ed.List(ed.ListIndex) = "December" Then
    aprdec.Delete Shift:=xlToLeft
    aprdec2.Delete Shift:=xlToLeft
    aprdecB.Delete Shift:=xlToLeft
    aprdec2B.Delete Shift:=xlToLeft
    aprdecC.Delete Shift:=xlToLeft
    Call Report1UpdatesWithQuarter

    ElseIf sd.List(sd.ListIndex) = "May" And ed.List(ed.ListIndex) = "May" Then
    maymay.Delete Shift:=xlToLeft
    maymayB.Delete Shift:=xlToLeft
    maymayC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "May" And ed.List(ed.ListIndex) = "June" Then
    mayjun.Delete Shift:=xlToLeft
    mayjunB.Delete Shift:=xlToLeft
    mayjunC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "May" And ed.List(ed.ListIndex) = "July" Then
    mayjul.Delete Shift:=xlToLeft
    mayjulB.Delete Shift:=xlToLeft
    mayjulC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "May" And ed.List(ed.ListIndex) = "August" Then
    mayaug.Delete Shift:=xlToLeft
    mayaugB.Delete Shift:=xlToLeft
    mayaugC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "May" And ed.List(ed.ListIndex) = "September" Then
    maysep.Delete Shift:=xlToLeft
    maysepB.Delete Shift:=xlToLeft
    maysepC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "May" And ed.List(ed.ListIndex) = "October" Then
    mayoct.Delete Shift:=xlToLeft
    mayoctB.Delete Shift:=xlToLeft
    mayoctC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "May" And ed.List(ed.ListIndex) = "November" Then
    maynov.Delete Shift:=xlToLeft
    maynovB.Delete Shift:=xlToLeft
    maynovC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "May" And ed.List(ed.ListIndex) = "December" Then
    maydec.Delete Shift:=xlToLeft
    maydecB.Delete Shift:=xlToLeft
    maydecC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "June" And ed.List(ed.ListIndex) = "June" Then
    junjun.Delete Shift:=xlToLeft
    junjunB.Delete Shift:=xlToLeft
    junjunC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "June" And ed.List(ed.ListIndex) = "July" Then
    junjul.Delete Shift:=xlToLeft
    junjulB.Delete Shift:=xlToLeft
    junjulC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "June" And ed.List(ed.ListIndex) = "August" Then
    junaug.Delete Shift:=xlToLeft
    junaugB.Delete Shift:=xlToLeft
    junaugC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "June" And ed.List(ed.ListIndex) = "September" Then
    junsep.Delete Shift:=xlToLeft
    junsepB.Delete Shift:=xlToLeft
    junsepC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "June" And ed.List(ed.ListIndex) = "October" Then
    junoct.Delete Shift:=xlToLeft
    junoctB.Delete Shift:=xlToLeft
    junoctC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "June" And ed.List(ed.ListIndex) = "November" Then
    junnov.Delete Shift:=xlToLeft
    junnovB.Delete Shift:=xlToLeft
    junnovC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "June" And ed.List(ed.ListIndex) = "December" Then
    jundec.Delete Shift:=xlToLeft
    jundecB.Delete Shift:=xlToLeft
    jundecC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "July" And ed.List(ed.ListIndex) = "July" Then
    juljul.Delete Shift:=xlToLeft
    juljulB.Delete Shift:=xlToLeft
    juljulC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "July" And ed.List(ed.ListIndex) = "August" Then
    julaug.Delete Shift:=xlToLeft
    julaugB.Delete Shift:=xlToLeft
    julaugC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "July" And ed.List(ed.ListIndex) = "September" Then
    julsep.Delete Shift:=xlToLeft
    julsep2.Delete Shift:=xlToLeft
    julsepB.Delete Shift:=xlToLeft
    julsep2B.Delete Shift:=xlToLeft
    julsepC.Delete Shift:=xlToLeft
    Call Report1UpdatesWithQuarter

    ElseIf sd.List(sd.ListIndex) = "July" And ed.List(ed.ListIndex) = "October" Then
    juloct.Delete Shift:=xlToLeft
    juloctB.Delete Shift:=xlToLeft
    juloctC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "July" And ed.List(ed.ListIndex) = "November" Then
    julnov.Delete Shift:=xlToLeft
    julnovB.Delete Shift:=xlToLeft
    julnovCB.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "July" And ed.List(ed.ListIndex) = "December" Then
    juldec.Delete Shift:=xlToLeft
    juldec2.Delete Shift:=xlToLeft
    juldecB.Delete Shift:=xlToLeft
    juldec2B.Delete Shift:=xlToLeft
    juldecC.Delete Shift:=xlToLeft
    Call Report1UpdatesWithQuarter

    ElseIf sd.List(sd.ListIndex) = "August" And ed.List(ed.ListIndex) = "August" Then
    augaug.Delete Shift:=xlToLeft
    augaugB.Delete Shift:=xlToLeft
    augaugC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "August" And ed.List(ed.ListIndex) = "September" Then
    augsep.Delete Shift:=xlToLeft
    augsepB.Delete Shift:=xlToLeft
    augsepC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "August" And ed.List(ed.ListIndex) = "October" Then
    augoct.Delete Shift:=xlToLeft
    augoctB.Delete Shift:=xlToLeft
    augoctC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "August" And ed.List(ed.ListIndex) = "November" Then
    augnov.Delete Shift:=xlToLeft
    augnovB.Delete Shift:=xlToLeft
    augnovC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "August" And ed.List(ed.ListIndex) = "December" Then
    augdec.Delete Shift:=xlToLeft
    augdecB.Delete Shift:=xlToLeft
    augdecC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "September" And ed.List(ed.ListIndex) = "September" Then
    sepsep.Delete Shift:=xlToLeft
    sepsepB.Delete Shift:=xlToLeft
    sepsepC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "September" And ed.List(ed.ListIndex) = "October" Then
    sepoct.Delete Shift:=xlToLeft
    sepoctB.Delete Shift:=xlToLeft
    sepoctC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "September" And ed.List(ed.ListIndex) = "November" Then
    sepnov.Delete Shift:=xlToLeft
    sepnovB.Delete Shift:=xlToLeft
    sepnovC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "September" And ed.List(ed.ListIndex) = "December" Then
    sepdec.Delete Shift:=xlToLeft
    sepdecB.Delete Shift:=xlToLeft
    sepdecC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "October" And ed.List(ed.ListIndex) = "October" Then
    octoct.Delete Shift:=xlToLeft
    octoctB.Delete Shift:=xlToLeft
    octoctC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "October" And ed.List(ed.ListIndex) = "November" Then
    octnov.Delete Shift:=xlToLeft
    octnovB.Delete Shift:=xlToLeft
    octnovC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "October" And ed.List(ed.ListIndex) = "December" Then
    octdec.Delete Shift:=xlToLeft
    octdec2.Delete Shift:=xlToLeft
    octdecB.Delete Shift:=xlToLeft
    octdec2B.Delete Shift:=xlToLeft
    octdecC.Delete Shift:=xlToLeft
    Call Report1UpdatesWithQuarter

    ElseIf sd.List(sd.ListIndex) = "November" And ed.List(ed.ListIndex) = "November" Then
    novnov.Delete Shift:=xlToLeft
    novnovB.Delete Shift:=xlToLeft
    novnovC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "November" And ed.List(ed.ListIndex) = "December" Then
    novdec.Delete Shift:=xlToLeft
    novdecB.Delete Shift:=xlToLeft
    novdecC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter

    ElseIf sd.List(sd.ListIndex) = "December" And ed.List(ed.ListIndex) = "December" Then
    decdec.Delete Shift:=xlToLeft
    decdecB.Delete Shift:=xlToLeft
    decdecC.Delete Shift:=xlToLeft
    ThisWorkbook.Sheets("Booked Services").Range("15:23,53:61").Delete xlUp
    ThisWorkbook.Sheets("Turnover per Entity").Range("49:91").Delete xlUp
    Call Report1UpdatesWithoutQuarter
    
    End If

    ' False False False False
    If CheckBox1.Value = False And CheckBox2.Value = False And CheckBox3.Value = False And CheckBox4.Value = False Then
    Sheets("Booked Services").Visible = False
    Sheets("Turnover per Entity").Visible = False
    Sheets("Air Report").Visible = False
    Sheets("Air Report (Online)").Visible = False
    MsgBox "Select at least one report.", vbOKOnly, "Sales Reporting Tool"
    Call CommandButton2_Click
    
    ' True False False False
    ElseIf CheckBox1.Value = True And CheckBox2.Value = False And CheckBox3.Value = False And CheckBox4.Value = False Then
    Sheets("Booked Services").Visible = True
    Sheets("Turnover per Entity").Visible = False
    Sheets("Air Report").Visible = False
    Sheets("Air Report (Online)").Visible = False
    
        ' True True False False
    ElseIf CheckBox1.Value = True And CheckBox2.Value = True And CheckBox3.Value = False And CheckBox4.Value = False Then
    Sheets("Booked Services").Visible = True
    Sheets("Turnover per Entity").Visible = True
    Sheets("Air Report").Visible = False
    Sheets("Air Report (Online)").Visible = False
    
        ' True True True False
    ElseIf CheckBox1.Value = True And CheckBox2.Value = True And CheckBox3.Value = True And CheckBox4.Value = False Then
    Sheets("Booked Services").Visible = True
    Sheets("Turnover per Entity").Visible = True
    Sheets("Air Report").Visible = True
    Sheets("Air Report (Online)").Visible = False
    
        ' True True True True
    ElseIf CheckBox1.Value = True And CheckBox2.Value = True And CheckBox3.Value = True And CheckBox4.Value = True Then
    Sheets("Booked Services").Visible = True
    Sheets("Turnover per Entity").Visible = True
    Sheets("Air Report").Visible = True
    Sheets("Air Report (Online)").Visible = True
    
    ' False True False False
    ElseIf CheckBox1.Value = False And CheckBox2.Value = True And CheckBox3.Value = False And CheckBox4.Value = False Then
    Sheets("Booked Services").Visible = False
    Sheets("Turnover per Entity").Visible = True
    Sheets("Air Report").Visible = False
    Sheets("Air Report (Online)").Visible = False
    
    ' False True True False
    ElseIf CheckBox1.Value = False And CheckBox2.Value = True And CheckBox3.Value = True And CheckBox4.Value = False Then
    Sheets("Booked Services").Visible = False
    Sheets("Turnover per Entity").Visible = True
    Sheets("Air Report").Visible = True
    Sheets("Air Report (Online)").Visible = False
    
    ' False True True True
    ElseIf CheckBox1.Value = False And CheckBox2.Value = True And CheckBox3.Value = True And CheckBox4.Value = True Then
    Sheets("Booked Services").Visible = False
    Sheets("Turnover per Entity").Visible = True
    Sheets("Air Report").Visible = True
    Sheets("Air Report (Online)").Visible = True
    
    ' False False True False
    ElseIf CheckBox1.Value = False And CheckBox2.Value = False And CheckBox3.Value = True And CheckBox4.Value = False Then
    Sheets("Booked Services").Visible = False
    Sheets("Turnover per Entity").Visible = False
    Sheets("Air Report").Visible = True
    Sheets("Air Report (Online)").Visible = False
    
    ' False False True True
    ElseIf CheckBox1.Value = False And CheckBox2.Value = False And CheckBox3.Value = True And CheckBox4.Value = True Then
    Sheets("Booked Services").Visible = False
    Sheets("Turnover per Entity").Visible = False
    Sheets("Air Report").Visible = True
    Sheets("Air Report (Online)").Visible = True
    
    ' False False False True
    ElseIf CheckBox1.Value = False And CheckBox2.Value = False And CheckBox3.Value = False And CheckBox4.Value = True Then
    Sheets("Booked Services").Visible = False
    Sheets("Turnover per Entity").Visible = False
    Sheets("Air Report").Visible = False
    Sheets("Air Report (Online)").Visible = True
    
    ' True False True False
    ElseIf CheckBox1.Value = True And CheckBox2.Value = False And CheckBox3.Value = True And CheckBox4.Value = False Then
    Sheets("Booked Services").Visible = True
    Sheets("Turnover per Entity").Visible = False
    Sheets("Air Report").Visible = True
    Sheets("Air Report (Online)").Visible = False
    
    ' True False False True
    ElseIf CheckBox1.Value = True And CheckBox2.Value = False And CheckBox3.Value = False And CheckBox4.Value = True Then
    Sheets("Booked Services").Visible = True
    Sheets("Turnover per Entity").Visible = False
    Sheets("Air Report").Visible = False
    Sheets("Air Report (Online)").Visible = True
    
    ' False True False True
    ElseIf CheckBox1.Value = False And CheckBox2.Value = True And CheckBox3.Value = False And CheckBox4.Value = True Then
    Sheets("Booked Services").Visible = False
    Sheets("Turnover per Entity").Visible = True
    Sheets("Air Report").Visible = False
    Sheets("Air Report (Online)").Visible = True
    
    ' True True False True
    ElseIf CheckBox1.Value = True And CheckBox2.Value = True And CheckBox3.Value = False And CheckBox4.Value = True Then
    Sheets("Booked Services").Visible = True
    Sheets("Turnover per Entity").Visible = True
    Sheets("Air Report").Visible = False
    Sheets("Air Report (Online)").Visible = True
    
    ' True False True True
    ElseIf CheckBox1.Value = True And CheckBox2.Value = False And CheckBox3.Value = True And CheckBox4.Value = True Then
    Sheets("Booked Services").Visible = True
    Sheets("Turnover per Entity").Visible = False
    Sheets("Air Report").Visible = True
    Sheets("Air Report (Online)").Visible = True
    
    End If
    
    Sheets("Reports").Select
    ActiveWindow.ScrollRow = 1
    
    Call ProtectAll
    
    UserForm1.Hide
    
    Application.ScreenUpdating = True

    End If
    
    UserForm5.Show

End Sub

As you can see in the If Then blocks (e.g. start month January and end month July) it will refer to another Sub, see example below:

Code:

Private Sub JanJanx()
    Sheets("Pivots").PivotTables("Draaitabel5").PivotFields("Month").PivotItems("February").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel5").PivotFields("Month").PivotItems("March").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel5").PivotFields("Month").PivotItems("April").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel5").PivotFields("Month").PivotItems("May").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel5").PivotFields("Month").PivotItems("June").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel5").PivotFields("Month").PivotItems("July").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel5").PivotFields("Month").PivotItems("August").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel5").PivotFields("Month").PivotItems("September").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel5").PivotFields("Month").PivotItems("October").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel5").PivotFields("Month").PivotItems("November").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel5").PivotFields("Month").PivotItems("December").Visible = False
    
    Sheets("Pivots").PivotTables("Draaitabel6").PivotFields("Month").PivotItems("February").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel6").PivotFields("Month").PivotItems("March").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel6").PivotFields("Month").PivotItems("April").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel6").PivotFields("Month").PivotItems("May").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel6").PivotFields("Month").PivotItems("June").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel6").PivotFields("Month").PivotItems("July").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel6").PivotFields("Month").PivotItems("August").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel6").PivotFields("Month").PivotItems("September").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel6").PivotFields("Month").PivotItems("October").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel6").PivotFields("Month").PivotItems("November").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel6").PivotFields("Month").PivotItems("December").Visible = False

    Sheets("Pivots").PivotTables("Draaitabel7").PivotFields("Month").PivotItems("February").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel7").PivotFields("Month").PivotItems("March").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel7").PivotFields("Month").PivotItems("April").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel7").PivotFields("Month").PivotItems("May").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel7").PivotFields("Month").PivotItems("June").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel7").PivotFields("Month").PivotItems("July").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel7").PivotFields("Month").PivotItems("August").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel7").PivotFields("Month").PivotItems("September").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel7").PivotFields("Month").PivotItems("October").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel7").PivotFields("Month").PivotItems("November").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel7").PivotFields("Month").PivotItems("December").Visible = False
    
    Sheets("Pivots").PivotTables("Draaitabel8").PivotFields("Month").PivotItems("February").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel8").PivotFields("Month").PivotItems("March").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel8").PivotFields("Month").PivotItems("April").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel8").PivotFields("Month").PivotItems("May").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel8").PivotFields("Month").PivotItems("June").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel8").PivotFields("Month").PivotItems("July").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel8").PivotFields("Month").PivotItems("August").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel8").PivotFields("Month").PivotItems("September").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel8").PivotFields("Month").PivotItems("October").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel8").PivotFields("Month").PivotItems("November").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel8").PivotFields("Month").PivotItems("December").Visible = False
    
    Sheets("Pivots").PivotTables("Draaitabel14").PivotFields("Month").PivotItems("February").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel14").PivotFields("Month").PivotItems("March").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel14").PivotFields("Month").PivotItems("April").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel14").PivotFields("Month").PivotItems("May").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel14").PivotFields("Month").PivotItems("June").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel14").PivotFields("Month").PivotItems("July").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel14").PivotFields("Month").PivotItems("August").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel14").PivotFields("Month").PivotItems("September").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel14").PivotFields("Month").PivotItems("October").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel14").PivotFields("Month").PivotItems("November").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel14").PivotFields("Month").PivotItems("December").Visible = False
    
    Sheets("Pivots").PivotTables("Draaitabel1").PivotFields("Month").PivotItems("February").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel1").PivotFields("Month").PivotItems("March").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel1").PivotFields("Month").PivotItems("April").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel1").PivotFields("Month").PivotItems("May").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel1").PivotFields("Month").PivotItems("June").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel1").PivotFields("Month").PivotItems("July").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel1").PivotFields("Month").PivotItems("August").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel1").PivotFields("Month").PivotItems("September").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel1").PivotFields("Month").PivotItems("October").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel1").PivotFields("Month").PivotItems("November").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel1").PivotFields("Month").PivotItems("December").Visible = False

    Sheets("Pivots").PivotTables("Draaitabel15").PivotFields("Month").PivotItems("February").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel15").PivotFields("Month").PivotItems("March").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel15").PivotFields("Month").PivotItems("April").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel15").PivotFields("Month").PivotItems("May").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel15").PivotFields("Month").PivotItems("June").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel15").PivotFields("Month").PivotItems("July").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel15").PivotFields("Month").PivotItems("August").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel15").PivotFields("Month").PivotItems("September").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel15").PivotFields("Month").PivotItems("October").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel15").PivotFields("Month").PivotItems("November").Visible = False
    Sheets("Pivots").PivotTables("Draaitabel15").PivotFields("Month").PivotItems("December").Visible = False
End Sub

Thanks in advance for your input!

Sam

Jerry Sullivan · Aug 7, 2015

Sam, You can consolidate that code quite a bit using a procedure that takes start and end months as parameters in lieu of having separate subs for each combination.

Here's an example that should filter your PivotTables based on the user selected months. To test this in a copy of your workbook, define Named Ranges "inpStartMonth" and "inpEndMonth". You can use Data Validation (DV) to have a drop down list in those two cells. I prefer not to use DV in lieu of a ComboBox Form Control. You're welcome to adapt the code to work with your ComboBoxes if you prefer those.

Paste this code into a Standard Code Module in your workbook...

Code:

Public Sub FilterSelectedMonths()
'--reads user inputs of start and end months then calls
'    procedure to filter specified pivot tables between those months

 Dim sStartMth As String, sEndMth As String
 Dim vPivotsToFilter As Variant
 Dim ws As Worksheet

 Set ws = ActiveSheet
 
 '--read user selections from data validation cells
 sStartMth = ws.Range("inpStartMonth")
 sEndMth = ws.Range("inpEndMonth")

 '--build array of pivot tables to be filtered
 With Sheets("Pivots")
   vPivotsToFilter = Array( _
      .PivotTables("Draaitabel1"), _
      .PivotTables("Draaitabel5"), _
      .PivotTables("Draaitabel6"), _
      .PivotTables("Draaitabel7"), _
      .PivotTables("Draaitabel8"), _
      .PivotTables("Draaitabel14"), _
      .PivotTables("Draaitabel15"))
 End With
 
 '--call procedure to filter each pivot by selected months
 Call FilterPivotMonths(vPivotsToFilter, sStartMth, sEndMth)
 
End Sub


Private Sub FilterPivotMonths(vPivotsToFilter As Variant, _
   sStartMth As String, sEndMth As String)

'--filters pivotfield named "Month" in each pivottable in array
'    so each month between start and end months (inclusive) are visible.

 Dim bShowAllMonths As Boolean
 Dim lStartMth As Long, lEndMth As Long, lNdx As Long, lMonth As Long
 Dim sItem As String
 
 '--convert month string to month number
 lStartMth = Month(DateValue(sStartMth & " 1,2000"))
 lEndMth = Month(DateValue(sEndMth & " 1,2000"))
 
 '--test for show all months - no filters
 bShowAllMonths = (lStartMth = lEndMth Mod 12 + 1)
  
 '--step through each pivottable
 For lNdx = LBound(vPivotsToFilter) To UBound(vPivotsToFilter)

   With vPivotsToFilter(lNdx)
      .ManualUpdate = True
      With .PivotFields("Month")
         .ClearAllFilters
         If Not bShowAllMonths Then
         
            '--begin with month after end month then hide each month's
            '    pivotitem until reaching start month
            lMonth = lEndMth Mod 12 + 1
            
            While lMonth <> lStartMth
               '--convert month nbr to string
               sItem = Format(DateSerial(2000, lMonth, 1), "mmmm")
               
               '--handle scenario of pivotitem doesn't exist
               On Error Resume Next
               .PivotItems(sItem).Visible = False
               On Error GoTo 0
               
               lMonth = lMonth Mod 12 + 1
            Wend
         End If
      End With ' .PivotFields("Month")
      .ManualUpdate = False
   End With 'vPivotsToFilter(lNdx)
 Next lNdx
   
End Sub

The code could be further optimized for speed, but I wanted to keep this simple unless you find that speed is a problem.

I don't fully understand the parts of your CommandButton1_Click code that use the selected months to delete ranges of data that are not needed.
I'd expect that a similar consolidation could be done to remove the redundancy in that code; however I'd have to understand more about what you are trying to accomplish with that code before I could help you with that.

SamP1983 · Aug 7, 2015

Hi Jerry,

Thanks a lot!!

I will study this and try to implement it in my code. I will get back to you on your question about all the parts of the CommandButton1_Click code.

Thanks, Sam

How to ignore PivotItems that don't exist when changing PivotField in vba

SamP1983

New Member

Excel Facts

Jerry Sullivan

MrExcel MVP

SamP1983

New Member

Jerry Sullivan

MrExcel MVP

SamP1983

New Member

Similar threads

Forum statistics

Share this page

How to ignore PivotItems that don't exist when changing PivotField in vba

SamP1983

New Member

Excel Facts

Jerry Sullivan

MrExcel MVP

SamP1983

New Member

Jerry Sullivan

MrExcel MVP

SamP1983

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock