Open workbook to weekly worksheet containing today's date?

swirlbread

New Member
Joined
Jul 15, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Good morning,

I have created a workbook that will function as a weekly schedule - each worksheet represents a new week. I would like to make it so when the workbook opens that it scans all of the worksheets and opens to the worksheet containing today's date. I have tried several different VBA codes and have been unsuccessful.

Some more information:
-Currently the worksheets are named as "Week of 8-2-21", "Week of 8-9-21", etc.
-Monday will be considered the first day of the week and each date is listed in cell B3 through H3 of each sheet. The remaining dates (starting in C3) are calculated using B3+1, etc. I have been just copying the previous worksheet and changing the date in B3.
-Dates in B3 are written in format mm/dd/yyyy but show up in the actual cell as Weekday, Month Day, Year

Thank you for any help you can provide! I am VERY new to macros and learning as I go, but this one has stumped me.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Sequoyah

Board Regular
Joined
Mar 26, 2017
Messages
66
Hi swirlbread,
insert this code in ThisWorkbook module - not a standard module
VBA Code:
Private Sub Workbook_Open()
    
    Dim ws          As Worksheet
    Dim MyDate      As String
    
    For Each ws In ThisWorkbook.Sheets
        MyDate = Right(ws.Name, Len(ws.Name) - 8)
        
        If Format(MyDate, "ww-yyyy") = Format(Date, "ww-yyyy") Then
            ws.Activate
            Range("A1").Select
        End If
        
    Next ws
    
End Sub
 
Solution

swirlbread

New Member
Joined
Jul 15, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi swirlbread,
insert this code in ThisWorkbook module - not a standard module
VBA Code:
Private Sub Workbook_Open()
   
    Dim ws          As Worksheet
    Dim MyDate      As String
   
    For Each ws In ThisWorkbook.Sheets
        MyDate = Right(ws.Name, Len(ws.Name) - 8)
       
        If Format(MyDate, "ww-yyyy") = Format(Date, "ww-yyyy") Then
            ws.Activate
            Range("A1").Select
        End If
       
    Next ws
   
End Sub
Thank you so much! This worked perfectly. I wasn't anywhere close when I tried. Appreciate it very much.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,110
Messages
5,768,150
Members
425,458
Latest member
Jaspal1996

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top