Hide a selection of columns within a range

[Grimm127]

Hi All,
I am stuck.
I have a Range of columns Range(C:AA) Each Column Header has a calendar month starting 01/01/2020 to YTD 2020 and continues 01/01/2021 to YTD 2021.
Each month, I need to have a macro that hides a set of columns. For Example:

I select Jan
Columns C (Jan-20); Column L (Oct-20); Column M (Nov-20); Column N (Dec-20) & Column P (Jan-21) will show and the rest within that range will be hidden

If I select Apr
Columns F (Apr-20); Column P (Jan-21); Column Q (Feb-21); Column R (Mar-21) & Column S (Apr-21) will show and the rest within that range will be hidden

I was thinking of using Row 2 as a marker. So if there is an 1(one) in Row 2 within the column range then it hides the column if there is a 0 (zero) in Row 2 then those remain unhidden.

Any suggestions?

Thanks!

 

[Grimm127]

It should affect a handful of tabs.
Tab Names: Income Statement, Maintenance, Safety and Finance

 

[Yongle]

It should affect ....
Income Statement, Maintenance, Safety and Finance

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim M1 As Date, M2 As Date, M3 As Date, M4 As Date, M5 As Date
    Dim TF As Boolean
    Dim c As Long
    
    Select Case Sh.Name
        Case "Income Statement", "Maintenance", "Safety", "Finance"
            
            If Target.Address(0, 0) = "AB1" Then
                If Not Target = "ALL" Then
                    M1 = CDate(1 & " " & Target & " " & 2020)
                    M2 = DateSerial(Year(M1), Month(M1) + 9, 1)
                    M3 = DateSerial(Year(M1), Month(M1) + 10, 1)
                    M4 = DateSerial(Year(M1), Month(M1) + 11, 1)
                    M5 = DateSerial(Year(M1), Month(M1) + 12, 1)
                
                    For c = 3 To 27
                        Select Case Cells(3, c)
                            Case M1, M2, M3, M4, M5:    TF = False
                            Case Else:                  TF = True
                        End Select
                        Sh.Columns(c).EntireColumn.Hidden = TF
                    Next c
                Else
                    Sh.Range("C:AA").EntireColumn.Hidden = False
                End If
            End If
    End Select
End Sub

 

[Grimm127]

Hi, So far it works very well. However, I am getting an error below when I select All. I thought "All" would unhide the entire field. Aside from that it's fantastic. The second error is strange and happens when I first start using the macro.

 

[Yongle]

The second error is because VBA can see TWO (or more) macros with the same name Printing_Format
This often happens when testing - search all code windows for a macro with that name and make sure you delete the correct one!

 

[Grimm127]

Ok, I found the duplicate and removed it. Let me know about the ALL when you can. That is the last piece of the puzzle. Thank you so much for your guidance. I have been playing with VBA many years but this is the first time I ever used it imbedded within the worksheets.

 

[Grimm127]

Thank you everything works. The last question that I have is this. Can the AB1 Reference be in another Tab completely separate from the tabs that it refreshes?

What if I want to Use the AB1 Cell in Tab "Driver"? I would like to do a Data Validation List box in that AB1 cell. Will that work?

 

[Yongle]

Is this your amended requirement?
Changing selection in dropdown in cell AB1 in "Driver" should simultaneously trigger change to visible columns in 4 tabs
( Income Statement, Maintenance, Safety and Finance)

 

[Grimm127]

Is this your amended requirement?
Changing selection in dropdown in cell AB1 in "Driver" should simultaneously trigger change to visible columns in 4 tabs
( Income Statement, Maintenance, Safety and Finance)

Exactly. I tried to do a Link to the Driver Tab to each of the AB1 Cells using a List. But what happens is when I switch the Months in the Driver Tab it doesn't do anything in the other four tabs. It only works if I manually type in a month and then enter

 

[Yongle]

In that case, the change in cell AB1 in sheet "Driver" becomes the trigger
I will post amended code tomorrow afternoon

 

[Grimm127]

In that case, the change in cell AB1 in sheet "Driver" becomes the trigger
I will post amended code tomorrow afternoon

Thank you for your time and patience. I am definitely learning something new and thankful.