Dynamic column hiding

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


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).


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
                           Selection.EntireColumn.Hidden = False
                       ElseIf counter > month_nmbr Then
                           Selection.EntireColumn.Hidden = True
                   End If
               Next i
           Next counter
   End If

End Sub


©2024 eLuminary LLC. All rights reserved.