Open workbook on todays date

Hi, jshaw82,

due to the file format itīs impossible to integrate macro code into an *.xlsx, you must either save as *.xlsm or *.xlsb and then copy the code into ThisWorkbook.

Your attachment shows an empty workbook - what shall be integrated where?

Ciao,
Holger

Hi, jshaw82,

sheet shows last year so maybe use this code in Workbook_Open:

Private Sub Workbook_Open()
Dim lngRow As Long

Application.ScreenUpdating = False

With Worksheets("Current Week")
    .Cells(3, 4) = .Cells(5, 5)
'    .Range("D3").Select
End With
With Worksheets("Rota")
    lngRow = WorksheetFunction.Match(CDbl(DateSerial(Year(Date) - 1, Month(Date), Day(Date))), .Columns(2), 0)
    Application.Goto .Cells(lngRow, 1), True
End With

Application.ScreenUpdating = True
    
End Sub

Ciao,
Holger

Hi, John,

the cells that the date is in merged

No, itīs simply that no match can be found if you search for a date in January 2012 and the dates in the sheet start with June 2012.

Private Sub Workbook_Open()
Dim varRes As Variant

Application.ScreenUpdating = False

With Worksheets("Current Week")
    .Cells(3, 4) = .Cells(5, 5)
'    .Range("D3").Select
End With
With Worksheets("Rota")
    On Error Resume Next
    varRes = WorksheetFunction.Match(CDbl(DateSerial(Year(Date) - 1, Month(Date), Day(Date))), .Columns(2), 0)
    On Error GoTo 0
    If (Not IsError(varRes) And Len(varRes) > 0) Then Application.Goto .Cells(varRes, 1), True
End With

Application.ScreenUpdating = True
    
End Sub

Ciao,
Holger

Hi, John,

because the other workbooks showed last year dates I used this line to reflect that:

    varRes = WorksheetFunction.Match(CDbl(DateSerial(Year(Date) - 1, Month(Date), Day(Date))), .Columns(2), 0)

If the dates are from 2013 the code may be simplified to

    varRes = WorksheetFunction.Match(CDbl(Date), .Columns(2), 0)

and row 396 is on top of the window (at least for today).

HTH,
Holger