Forum Discussion

Radhish Kartha's avatar
Radhish Kartha
Copper Contributor
Jun 12, 2018

Need to hide sheet in a workbook based on the calendar day of the month

Hi Team,

 

I have a sheet with 31 sheets corresponding to maximum 31 days in a month.

 

I need to hide all the sheets that are in the future based on the calendar day of the month.

 

For e:g If today is 15th June then 15 sheets named from 1-15 should be visible. The other sheets from 16-31 should be hidden and not available for view. How do I achieve this?

Macros & VBA
Need Help
  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    Jun 12, 2018

    Hey Radhish-

     

    Try adding a Workbook_Open event to the ThisWorkbook code module:

     

    1. Paste this code to the ThisWorkBook code module

    2. Save File as .xlsm

    3. Open File (The code will run when macros are enabled and the associated sheets will be hidden)

     

    Private Sub Workbook_Open()

    Dim sht As Worksheet
    Dim today As Integer
    
    today = Day(Date)
    
    For Each sht In ActiveWorkbook.Worksheets
        
        If sht.Name <= today Then
            sht.Visible = True
        Else
            sht.Visible = False
        End If
    
    Next sht

End Sub

     

     

    • Matt Mickle's avatar
      Matt Mickle
      Bronze Contributor
      Jun 14, 2018

      Radhish-

       

      Just wanted to follow up and see if you were able to get the solution to work and see if you might have any questions on how it works or how to implement it.  Please feel free to post back to the Excel Community if you have anymore issues.

Resources