Forum Discussion

Nicarlov's avatar
Nicarlov
Copper Contributor
Jul 01, 2019

Excel Column Management

I am not an Excel guru, but I do have an Excel2013 book by Microsoft.  I have a budget in Excel and the columns are identified by month.  I would like to automatically hide the previous months and directly focus on the current month.  So when I open excel the next month, the only column I see is that month.  So today is July 1st, when I open this spreadsheet, I should not see the previous months I should only see July 19 (this would apply to any days within July 19).  When I open this spreadsheet in Aug 19, I should only see Aug.  Thank you for your patience and help.

excel

1 Reply

  • Smitty Smith's avatar
    Smitty Smith
    Former Employee
    Jul 01, 2019

    Nicarlov You can add this code to the the ThisWorkbook module:

     

    Private Sub Workbook_Open()
    Dim i As Long
    Dim strMonth As String
    
    strMonth = Format(Date, "mmmm")
    'Debug.Print strMonth
    
'   Unhide all month columns
    Range("B1:M1").EntireColumn.Hidden = False
    
'   Loop through each column - If it's <> to this month, then hide it
    For i = 2 To 13
        If Cells(1, i).Value <> strMonth Then
            Cells(1, i).EntireColumn.Hidden = True
        End If
    Next i
End Sub

    If you're not familiar with VBA code, then press Alt+F11 to open the VB Editor. Next, find the module called ThisWorkbook, double-click it, then paste the code into the new window that opens on the right. You can test it by pressing F5. Alt+Q will exit you back to Excel, and the code will run each time you open the workbook.

     

    HTH

Resources