Hide Columns based on Month Selection from the DropDown

Question

Hello,

I have around 30 different tabs (business development, customer success, coaches, development, finance, office admin, IT, etc...) in a file.

I have a Master tab, where there is a drop-down menu, which is linked to all other tabs in the file. If I select a month in this drop-down, it will show up in all tabs in cell A2

What I would like to do is when I select a month in the drop-down menu, I would like to hide rest of the months.

So, in the example below. I selected April in cell A2, I would like to hide Columns F to N (Actual results) and hide T to AB (Budgeted Numbers) in the same tab. I have around 30 tabs where I would like to apply this logic by simply using the dropdown menu.

It would be great, if you can share any ideas on how to achieve this. 

Many Thanks,

Ketul

Answer 1

Under the code that reads:

   Range(Columns(k), Columns(14)).EntireColumn.Hidden = True

add this code

   Range(Columns(k + 14), Columns(28)).EntireColumn.Hidden = True

best wishes

Answer 2

I'm interested as well.

Answer 3

Hi Carole,

Basically, what you can do is at the end of the macro, before the End Sub you can list the name of the other macros which you would like to run.

or

Sub HideBlankMonths()

Macro 1

Macro 2

Macro 3

Macro 4

End Sub

This worked for me. Hope it helps you as well.

Thanks

Answer 4

Hi

Here is an other approach plus a second macro to unhide every columns in all sheets.

I assume Sheets("FX Rate Master") is your Master Sheet, change it if it's not.

Place the macro in your Master sheet and it should work for all sheets.

If you want to exclude the Master Sheet  then remove the Apostrophe on those two lines.

'If Sh.Name = Sheets("FX Rate Master").Name Then GoTo nextsh:

and " nextsh:"

Sub SelectMonth()

    Dim Sh As Worksheet, Mth As String

    Application.ScreenUpdating = False

    Call unhide

    Mth = Cells(2, 1).Value

    For Each Sh In ThisWorkbook.Worksheets

    'If Sh.Name = Sheets("FX Rate Master").Name Then GoTo nextsh:

       Sh.Select

        Select Case Mth

            Case "Jan"

                Range("C1:M1,Q1:AA1").EntireColumn.Hidden = True

            Case "Feb"

                Range("D1:N1,R1:AB1").EntireColumn.Hidden = True

            Case "Mar"

                Range("C1,E1:N1,Q1,S1:AB1").EntireColumn.Hidden = True

            Case "Apr"

                Range("C1:D1,F1:N1,Q1:R1,T1:AB1").EntireColumn.Hidden = True

            Case "May"

                Range("C1:E1,G1:N1,Q1:S1,U1:AB1").EntireColumn.Hidden = True

            Case "Jun"

                Range("C1:F1,H1:N1,Q1:T1,V1:AB1").EntireColumn.Hidden = True

            Case "Jul"

                Range("C1:G1,I1:N1,Q1:U1,W1:AB1").EntireColumn.Hidden = True

            Case "Aug"

                Range("C1:H1,J1:N1,Q1:V1,X1:AB1").EntireColumn.Hidden = True

            Case "Sep"

                Range("C1:I1,k1:N1,Q1:W1,Y1:AB1").EntireColumn.Hidden = True

            Case "Oct"

                Range("C1:J1,L1:N1,Q1:X1,Z1:AB1").EntireColumn.Hidden = True

            Case "Nov"

                Range("C1:K1,M1:N1,Q1:Y1,AA1:AB1").EntireColumn.Hidden = True

            Case "Dec"

                Range("C1:L1,N1,Q1:Z1,AB1").EntireColumn.Hidden = True

        End Select

'nextsh:

    Next Sh

    Sheets("FX Rate Master").Select

    Application.ScreenUpdating = True

End Sub

Sub unhide()

    Dim Sh As Worksheet

    For Each Sh In ThisWorkbook.Worksheets

        Sh.Select

        Cells.EntireColumn.Hidden = False

    Next

    Sheets("FX Rate Master").Select

End Sub