Opening a spreadsheet at cell with today's date

steely_dan

New Member
Joined
Nov 16, 2005
Messages
5
Hi all.

A very simple question (I hope), but one that's got me going round in circles. I have a few spreadsheets in which column A is a list of historic and future dates, in ascending order, i.e. latest dates are lower down the list. What I want to do is make it easier for the users by moving automatically to the row containing today's date on opening, rather than the users having to scroll down to get there.

Anyone got any ideas please?

Cheers
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
WELCOME TO THE BOARD!

This will require a little VBA.
Right click on the Sheet tab name and select View Code.
In the VB Project Explore, you should see Sheet module name like “Sheet1” highlighted.
Under it, you should see an object named “ThisWorkbook”.
Double click on “ThisWorkbook” and paste this code in the resulting window.
Code:
Private Sub Workbook_Open()

    Application.ScreenUpdating = False

    Dim i As Long
    For i = Range("A65536").End(xlUp).Row To 1 Step -1
        If Cells(i, 1) = Date Then
            Cells(i, 1).Select
            Exit For
        End If
    Next i
     
    Application.ScreenUpdating = True
    
End Sub
As long as the date exists, when the file is opened, it will go to that cell in column A.
 
Upvote 0
Assuming that the dates are sorted and it is in sheet1 and column A, you can put this code in thisworkbook.

Private Sub Workbook_Open()
Sheet1.Range("A65535").End(xlUp).Select
End Sub
 
Upvote 0
Thanks guys - such speedy replies. Very impressive!
jmiskey - your works just fine, but there's a couple of things...

Firstly, while today's date is highlighted as the active cell, it is several screens further down the list. Is there a way of making the page scroll down to it, as that is the real point of the query?

Secondly, does it have to be sheet1 and column A, or can it be whatever range on whatever sheet name I give it? I've tried playing around with the code and substituting it with a new column range, but it makes no difference. I can always put a copy of the date range in column A as a control and hide it from the user, but it would be nice to do it in a smarter way than that! And there's nothing obvious in the code to change into a new sheet name, so does this only ever apply to "sheet1"?

Thanks again for your assistance.
 
Upvote 0
Here is the codes with some updates. At the beginning, it specifies which sheet to run on. Change as needed. Also, to change the column to run on simply change the three references that include column "A" (i.e. Range("A65536") and Cells(i, "A")).

I am still working on the scrolling part.
Code:
Private Sub Workbook_Open()

    Application.ScreenUpdating = False
    
    Dim i As Long
    
    Sheets("Sheet1").Activate
    For i = Range("A65536").End(xlUp).Row To 1 Step -1
        If Cells(i, "A") = Date Then
            Cells(i, "A").Select
            Exit For
        End If
    Next i
      
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
OK, I found it. Here is the code which will scroll to the cell where today's date is found:
Code:
Private Sub Workbook_Open()

    Application.ScreenUpdating = False
    
    Dim i As Long
    
'   Find cell with today's date
    Sheets("Sheet1").Activate
    For i = Range("A65536").End(xlUp).Row To 1 Step -1
        If Cells(i, "A") = Date Then
            Cells(i, "A").Select
            Exit For
        End If
    Next i
    
'   Scroll to selected cell
    With ActiveWindow
        .ScrollRow = ActiveCell.Row
        .ScrollColumn = ActiveCell.Column
    End With
      
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
Excellent JM. It does just what it says on the tin. Now I'll start playing around and customising it.
Thanks again.
Mark
 
Upvote 0

Forum statistics

Threads
1,226,456
Messages
6,191,144
Members
453,643
Latest member
adamb83

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
Back
Top