excel: want to scroll to a row for day 1 of current week

April 7, 2012 at 10:43:40
Specs: Windows Vista
I've got an excel spreadsheet I'm using as a running log and A4:A369 contains all of the dates of the year. I'd like to automatically scroll when sheet is opened to weekday(1) Sunday of weeknum(today()) the current week.

The hope is my training log will "jump" to the current week. I've already got the conditional formatting to highlight the row for the current day.

See More: excel: want to scroll to a row for day 1 of current week

Report •

April 7, 2012 at 11:24:56
This code should do what you asked for.

I suggest that you try this in a backup copy of your workbook in case something goes terribly wrong.

1 - Press Alt-F11 to open the VBA editor.
2 - Find your workbook name in the left hand pane (VBAProject)
3 - Double click the ThisWorkbook module
4 - Paste the code below into right hand pane
5 - If Sheets(1) is not the correct sheet, modify the code as required.
6 - Save the workbook
7 - Close it
8 - Open it
9 - Smile if it works

Private Sub Workbook_Open()
'Determine date of Sunday in current week
 curSunday = (Date - Choose(Weekday(Date), 0, 1, 2, 3, 4, 5, 6))
'Find that date in Sheet 1 Column A
   With Sheets(1).Columns(1)
    Set s = .Find(curSunday)
   End With
'Select that cell
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

April 7, 2012 at 11:45:01
Thank you very much. Script worked well.

Report •

Related Solutions

Ask Question