Dynamic column hiding

Question

I would like a spreadsheet that allows me to automatically hide columns which represent Months. My problem is that every month I have to produce management packs and I am continuously hiding columns. For example I am doing a month end for May. I will have to hide all the months post May.

I have a spreadsheet that has 3 tabs a tab 1)mapping sheet contaings the month and company info 2)P&L 3)Balance Sheet.

I am unaware on how I can getto automatically hide all the dates post May or whatever month I am using (defined on tab 1).

Answer

this is the code

assuming cell B1 is the cell that defines current Month and is in date formatting like 31/05/2010 your sheet names will be Tab1, tab2, tab3 and your months are in column D:O starting from January (Column D) and ending with December (column O)

You might need to change it to get it fitted with your worksheet. Please fell free to change it

go to tab1 then right click the tab name, click on view code and copy paste the code

Below is your code

---

Sub Worksheet_Change(ByVal Target As Range)

   If Target.Address = ("$B$1") Then
   
       month_nmbr = Month(Sheets("Tab1").Range("B1").Value)
       
           For counter = 1 To 12
               For i = 1 To 3
                   Sheets("Tab" & i).Activate
                       clmn = counter + 3
               
                       If counter <= month_nmbr Then
                                  
                           ActiveSheet.Columns(clmn).Select
                           Selection.EntireColumn.Hidden = False

                       ElseIf counter > month_nmbr Then
                                   
                           ActiveSheet.Columns(clmn).Select
                           Selection.EntireColumn.Hidden = True
           
                   End If
           
               Next i
              
           Next counter
      
       Sheets("Tab1").Select
      
   End If
   

End Sub