VBA - Hide Multiple Sheets Based On Date

[foolishpiano]

Hello everyone! I currently have a workbook that is updated daily, and I've created a macro that captures the data and creates a new worksheet in the workbook dated with the current date. I've included what I think is all of the relevant code below (I'm happy to post the full code if that would be helpful as well):

' Create new worksheet with today's date
        Dim szTodayDate As String
        szTodayDate = Format(Date, "mmm-dd-yyyy")
        On Error GoTo MakeSheet
        Sheets(szTodayDate).Activate
        Exit Sub
MakeSheet:
        Sheets.Add , Worksheets(Worksheets.count)
        ActiveSheet.Name = szTodayDate
' Copy table data from FTS to new worksheet
    Worksheets("Table Data from FTS").ListObjects("Table_query__1").Range.Copy
    ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues

What I'm wondering is: is it possible to add to the code a line that will go through the workbook and hide all worksheets that were created over a week ago? The number of open worksheets is starting to become ridiculous, and does not need to be visible all of the time. This data is just for historical record to be recalled if needed.

Thank you all for your help!

 

[Fluff]

If you normal date format is MM/DD/YYYY then you could try

Dim ws As Worksheet
For Each ws In Worksheets
   If ws.name < Date - 7 Then
      ws.Visible = False
   End If
Next ws

 

[foolishpiano]

Thank you so much for your reply, Fluff! Your code worked a little too well; all of the worksheets except the new one were hidden. My code is now:

' Turn off screen updating
    Application.ScreenUpdating = False
' Hide worksheets older than seven days ago
    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Name < Date - 7 Then
        ws.Visible = False
    End If
    Next ws
    ' Create new worksheet with today's date
        Dim szTodayDate As String
        szTodayDate = Format(Date, "mm-dd-yyyy")
        On Error GoTo MakeSheet
        Sheets(szTodayDate).Activate
        Exit Sub
MakeSheet:
        Sheets.Add , Worksheets(Worksheets.count)
        ActiveSheet.Name = szTodayDate
' Copy table data from FTS to new worksheet
    Worksheets("Table Data from FTS").ListObjects("Table_query__1").Range.Copy
    ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues

Any idea why they all hide themselves?

 

[Fluff]

What is your standard date format?

 

[foolishpiano]

"mm-dd-yyyy" is how my worksheets are named, i.e. 06-25-2018, 06-22-2018, 06-21-2018, etc.. I would include a screenshot of the workbook, but I apologize I'm not sure how.

 

[Fluff]

Is MM/DD/YYYY your regional date format, or do you use a European style date format of DD/MM/YYYY

 

[foolishpiano]

MM/DD/YYYY is my regional date format.

 

[Fluff]

Not sure why that happens, with UK dates if I have sheet names 17-06-2018 & 19-06-2018 then the 1st sheet is hidden but the other isn't.
Try adding this msgbox

   If ws.name < Date - 7 Then
     [COLOR=#0000ff] MsgBox CLng(CDate(ws.name)) & vbLf & CLng(Date - 7)[/COLOR]
      ws.Visible = False
   End If

What happens?

 

[foolishpiano]

The message box returned:

43256
43269

then

43257
43269

then

43258
43269

then

43259
43269

then

43262
43269

then

43264
43269

then

43265
43269

then

43266
43269

then

43269
43269

then

43271
43269

then

43272
43269

then

43273
43269

To my eyes it appears as though it's checking the dates correctly.

 

[Fluff]

Odd, How about

Dim ws As Worksheet
For Each ws In Worksheets
   If IsDate(ws.name) Then
      If CDate(ws.name) < Date - 7 Then ws.Visible = False
   End If
Next ws