VBA - Hide Multiple Sheets Based On Date

foolishpiano

New Member
Joined
Aug 19, 2016
Messages
23
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):

Code:
' 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!
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,095
Office Version
  1. 365
Platform
  1. Windows
If you normal date format is MM/DD/YYYY then you could try
Code:
Dim ws As Worksheet
For Each ws In Worksheets
   If ws.name < Date - 7 Then
      ws.Visible = False
   End If
Next ws
 

foolishpiano

New Member
Joined
Aug 19, 2016
Messages
23
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:

Code:
' 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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,095
Office Version
  1. 365
Platform
  1. Windows
What is your standard date format?
 

foolishpiano

New Member
Joined
Aug 19, 2016
Messages
23

ADVERTISEMENT

"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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,095
Office Version
  1. 365
Platform
  1. Windows
Is MM/DD/YYYY your regional date format, or do you use a European style date format of DD/MM/YYYY
 

foolishpiano

New Member
Joined
Aug 19, 2016
Messages
23

ADVERTISEMENT

MM/DD/YYYY is my regional date format.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,095
Office Version
  1. 365
Platform
  1. Windows
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
Code:
   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

New Member
Joined
Aug 19, 2016
Messages
23
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,095
Office Version
  1. 365
Platform
  1. Windows
Odd, How about
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,655
Messages
5,524,139
Members
409,562
Latest member
meeranaskar

This Week's Hot Topics

Top