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!
 

foolishpiano

New Member
Joined
Aug 19, 2016
Messages
23
That got it; thank you so much! I really appreciate all of your help. Out of curiosity, what is this new code doing that the other code wasn't doing?
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows
It checks if the sheet name can be converted to a valid date & if it can, it converts it prior to checking if it's more than a week old.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,112,788
Messages
5,542,520
Members
410,559
Latest member
jordansmith6532
Top