Hide sheets on date

[eddiee]

hi I have a simple work book with 12 worksheets one for each month.
what I need is on open of the work book the relevant month sheet must open the others must stay hidden.
I have tried something like below but cannot get it to work

If Day(Date) <= ("01/01/2016") Then

Sheets("January 2016").Visible = False
Else
Sheets("January 2016").Visible = True

End If
-------------------------------------------------------------------------------
Mod edit: Question moved to appropriate section

 

[Asheesh]

Welcome to the forum..

Just double checking - So your sheet name pattern is always Month Year

 

[eddiee]

Welcome to the forum..

Just double checking - So your sheet name pattern is always Month Year

hi Thanks for getting back to me
Yes these sheets are named month then year
January 2016 February 2016 and so on

 

[vletm]

@eddiee
Tab name is 'string' not 'date'.
You could compare 'string' with 'string' like next.

Private Sub Workbook_Open()
    Tabs = Worksheets.Count
    this_month = Format(Date, "mmmm yyyy")
    For t = 1 To Tabs
        With Sheets(t)
            SV = False
            If .Name = this_month Then SV = True
            .Visible = SV
        End With
    Next t
End Sub

 

[eddiee]

thanks Veltm
tried this and it worked but returned an error
changed the For t = 1 To Tabs to For t = 1 To 12
then seemed to work ok
the only other thing is I had two December tabs one for 2015 and the other for 2016 and it left both open?

 

[vletm]

@eddiee
You wrote that You had 12 worksheets ... 12 is 12!
What value do Tabs get? Check with Watch.
But, If Tabs value is >= 12 then no matter,
it should hide today all worksheets except 'December 2015'.
If still challenge, send that file for me.

 

[eddiee]

Hi vletm
Yes silly me December to December is 13 not 12
but opened the work book today and checked all is working just as you wrote it for me
Thanks a mill for your help saved me a great lot of time and headaches