VBA to open a worksheet on the closest date to today's date

[freddie mitchell]

Hello everyone,

I hope you can help me with this query. I'm using Excel 265.

I have a worksheet that has around 100 events and their respective dates listed in 2 separate columns. I'd like to create a code that will open the worksheet on the cell that closest matches today's date.
For example, if the next event is on the 31st October 2020, then the worksheet should open to this date.

So far my very limited VBA code generates a error because I rarely open the worksheet on the exact date that an event occurs.

I'd be very grateful if someone could help with this.

Thank you and apologies if this has already been covered, I didn't find a relevant thread when I looked earlier.

Freddie

 

[John_w]

Try this in the ThisWorkbook module. Assumes your sheet is named "Sheet1" and the dates are in column A.

Private Sub Workbook_Open()

    Dim r As Variant
    
    With Worksheets("Sheet1")
        r = Application.Match(CLng(Date), .Columns(1), 1)
        If .Cells(r, 1).Value >= Date Then
            .Cells(r, 1).Select
        Else
            .Cells(r + 1, 1).Select
        End If
    End With
    
End Sub

Save, close and reopen the workbook to test.

 

[freddie mitchell]

It works perfectly, thank you so much John, you're a super star!

 

[russelldt]

Try this in the ThisWorkbook module. Assumes your sheet is named "Sheet1" and the dates are in column A.

Private Sub Workbook_Open()

    Dim r As Variant
   
    With Worksheets("Sheet1")
        r = Application.Match(CLng(Date), .Columns(1), 1)
        If .Cells(r, 1).Value >= Date Then
            .Cells(r, 1).Select
        Else
            .Cells(r + 1, 1).Select
        End If
    End With
   
End Sub

Save, close and reopen the workbook to test.

I have a similar problem, but my dates are in row 6. I have tried, unsuccessfully, to modify the script, as below:

Private Sub Workbook_Open()

Dim r As Variant

With Worksheets("Cashflow")
r = Application.Match(CLng(Date), .Rows(6), 6)
If .Cells(r, 6).Value >= Date Then
.Cells(r, 6).Select
Else
.Cells(r + 6, 6).Select
End If
End With

End Sub

Would appreciate any help. Thanks

 

[John_w]

Welcome to MrExcel forums.

I have a similar problem, but my dates are in row 6

Try this:

Private Sub Workbook_Open()

    Dim c As Variant
    
    With Worksheets("Cashflow")
        c = Application.Match(CLng(Date), .Rows(6), 1)
        If .Cells(6, c).Value >= Date Then
            .Cells(6, c).Select
        Else
            .Cells(6, c + 1).Select
        End If
    End With
    
End Sub

Note - the Cells object arguments are Cells(row,column), hence the .Cells(6, c) for row 6 and the found column number.

 

[russelldt]

Welcome to MrExcel forums.

Try this:

Private Sub Workbook_Open()

    Dim c As Variant
   
    With Worksheets("Cashflow")
        c = Application.Match(CLng(Date), .Rows(6), 1)
        If .Cells(6, c).Value >= Date Then
            .Cells(6, c).Select
        Else
            .Cells(6, c + 1).Select
        End If
    End With
   
End Sub

Note - the Cells object arguments are Cells(row,column), hence the .Cells(6, c) for row 6 and the found column number.

If my starting column for the date is F, do I change the Rows(6), 1 to Rows(6), 6?

 

[John_w]

No, c = Application.Match(CLng(Date), .Rows(6), 1) is calling the Match worksheet function:

WorksheetFunction.Match method (Excel)

Office VBA reference topic

docs.microsoft.com

1st argument is the current date, as a number. 2nd argument, Rows(6), is the lookup_array/range. 3rd argument, 1, is the match_type:

If match_type is 1, Match finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order

If the code isn't working we need to know more. Is it selecting the wrong cell or something else?

Are the 01-Jan, 08-Jan, etc. dates or text? Is row 6 the header row of a table or an autofilter?

 

[russelldt]

No, c = Application.Match(CLng(Date), .Rows(6), 1) is calling the Match worksheet function:

WorksheetFunction.Match method (Excel)

Office VBA reference topic

docs.microsoft.com

1st argument is the current date, as a number. 2nd argument, Rows(6), is the lookup_array/range. 3rd argument, 1, is the match_type:


If the code isn't working we need to know more. Is it selecting the wrong cell or something else?

Are the 01-Jan, 08-Jan, etc. dates or text? Is row 6 the header row of a table or an autofilter?

Thanks for the prompt. Here is my final solution, that seems to work.

Private Sub Workbook_Open()

Dim c As Variant

With Worksheets("Cashflow")
c = Application.Match(CLng(Date), .Rows(6), 1)
If .Cells(6, c).Value >= Date Then
.Cells(6, c).Select
Else
.Cells(6, c + 6).Select
End If
End With

End Sub


Much appreciated your help