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

Here is a start; I have hidden only in the C to D range but extending it should be simple.

The TryMe() sub could be truned into a WorkSheet_OnChange sub but would be needed to be places in the Code module of every worksheet.

I have made a loop to do all but the First sheet.

Of course, you will need to run this every time you change the month on the front sheet.

The MsgBox code is just there for testing purposes.

best wishes

Sub WorksheetLoop()

         WS_Count = ActiveWorkbook.Worksheets.Count

         MsgBox WS_Count

         For n = 2 To WS_Count

            Call TryMe

         Next n

 End Sub

Sub TryMe()

  Columns("F:N").EntireColumn.Hidden = False

  mytest = Range("A2").Value

  myflag = False

  For j = 3 To 14

    MsgBox Cells(4, j)

    If Cells(4, j).Value = mytest Then

      myflag = True

      k = j + 1

      Exit For

    End If

  Next j

  If myflag and k < 14 Then

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

  End If

End Sub

Answer 3

Hi Bernard, 

Thank you very much for the code.

It worked fine for the first part.

I would also like to hide budget columns T to AB if I have selected April in Cell A2.

Thanks again.

Answer 4

Bernard,

It worked!

Thank you so much for your help.

Regards,

Ketul

Answer 5

Bernard,

One more question. How do I run all the macros at once ?

There are 30 different sheets I have inserted this code. I have to do it 30 times right now.

Can it be only one click operation ?

Thanks