find current date

[mgbcab]

I have a workbook where each tab represents a week. The tab is named by the date of that Monday, so this week would be "Mar 7". In each tab, cell B1 has this same date, also the dates of the current week are listed in B3 to F3.
What I would like to do is when the user opens up the file, it automatically goes to today's date's column or even the current week tab is fine.

 

[GTO]

I would think of looking for the dates on the sheets, presuming they are valid dates. Maybe:

In ThisWorkbook Module:

Option Explicit
    
Private Sub Workbook_Open()
Dim lDate As Long
Dim wks As Worksheet
Dim i As Long
    
    lDate = CLng(Date)
    For Each wks In ThisWorkbook.Worksheets
        For i = 2 To 6
            If wks.Cells(3, i).Value2 = CLng(Date) Then
                Application.Goto wks.Cells(3, i)
                GoTo Jumpout
            End If
        Next
    Next
Jumpout:
    
'Remaining code...
End Sub

Hope that helps,

Mark

 

[mgbcab]

I get an error message "Ambigous name detected:Workbook open". I do have another workbook open macro before it. I assume this goes in ThisWorkbook?

 

[GTO]

I get an error message "Ambigous name detected:Workbook open". I do have another workbook open macro before it. I assume this goes in ThisWorkbook?

Yes, it does go in ThisWorkbook module.

You cannot have two same-named procedures (a Sub or Function) in the same module. You will want to incorporate just the code and variable deeclarations and meld it with what you already have.

Hope that helps,

Mark

 

[mgbcab]

Yes, it does go in ThisWorkbook module.

You cannot have two same-named procedures (a Sub or Function) in the same module. You will want to incorporate just the code and variable deeclarations and meld it with what you already have.

Hope that helps,

Mark

Got it, I included your code with my existing and it worked. Thanks