Dynamic column hiding

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:26 PM GMT

Question

Profit and Loss page example

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

Advertisement

©2022 eLuminary LLC. All rights reserved.