Hide Sheet if Date is Cell is passed

[hajiali]

When the workbook opens I want it hide all sheets if date in Range("P1") is passed. I tried this code however it hides some sheet that the date has not passed and has other sheets visible when date is passed

Private Sub Workbook_Open()
Dim ws As Worksheet

For Each ws In Worksheets
    If Range("P1") < Now Then
        ws.Visible = xlSheetHidden
    'Else
        'ws.Visible = xlSheetVisible
    End If
Next ws
Set ws = Nothing
End Sub

 

[Joe4]

Just looping through worksheets does not actually open or activate them.

So this reference:

If Range("P1") < Now Then

will be checking the same Range("P1") on the active sheet a whole bunch of times (precisely the number of sheets there are).

You need to qualify the sheet reference, like you did for "ws.Visible..", i.e.

If ws.Range("P1") < Now Then

 

[hajiali]

Thanks Joe4 If sheets are named "1", "2","3", "4"...etc how would it modify the code to loop through each sheet and compare the dates of each sheet.

 

[Joe4]

Thanks Joe4 If sheets are named "1", "2","3", "4"...etc how would it modify the code to loop through each sheet and compare the dates of each sheet.

You don't need to do any of that.
Just put "ws." in from the Range("P1") like I showed you, and your code should then wotk they way you want.

 

[hajiali]

sorry. I was wondering what the difference was between the two. Totally missed it.