Scroll down to today's date

[pontiff]

Hi,
I'm using the following script from another thread to try to have my worksheet (sheet1) scroll down to the row containing today's date on opening.

Private Sub Workbook_Open()
Worksheets(“Sheet1”).Select
x = Format(Date, "dd/mm/yyyy")
On Error Resume Next
Worksheets(“Sheet1”).Columns(2).Find(What:=x, LookIn:=xlValues).Activate
Application.Goto Selection, True
End Sub

My dates are in column B as dd/mm/yyyy .

It seems to select the correct date (green box appears around 06/03/2018) but doesn't scroll down/up to it.
Any advice much appreciated.

 

[HackSlash]

Private Sub Workbook_Open()
    Worksheets(“Sheet1”).Select
    x = Format(Date, "dd/mm/yyyy")
    'On Error Resume Next
    Worksheets(“Sheet1”).Columns(2).Find(What:=x, LookIn:=xlValues).Activate
    Application.Goto Selection, True

    ActiveWindow.ScrollRow = Selection.Row
    ActiveWindow.ScrollColumn = Selection.Column
End Sub

 

[pontiff]

Private Sub Workbook_Open()
    Worksheets(“Sheet1”).Select
    x = Format(Date, "dd/mm/yyyy")
    'On Error Resume Next
    Worksheets(“Sheet1”).Columns(2).Find(What:=x, LookIn:=xlValues).Activate
    Application.Goto Selection, True

    ActiveWindow.ScrollRow = Selection.Row
    ActiveWindow.ScrollColumn = Selection.Column
End Sub

Thanks for the reply, I still cant get it to work. To enter the code I'm right clicking on "Sheet1", selecting view code and pasting it there. Is there something I'm doing wrong?

 

[Joe4]

Thanks for the reply, I still cant get it to work. To enter the code I'm right clicking on "Sheet1", selecting view code and pasting it there. Is there something I'm doing wrong?

Yes. Workbook_Open event procedure code needs to go in the "ThisWorkbook" module, not in any of the Sheet modules.

 

[pontiff]

Yes. Workbook_Open event procedure code needs to go in the "ThisWorkbook" module, not in any of the Sheet modules.

Thanks. I've moved it now but am getting a "Runtime error 9 subscript out of range" error on this line....

Worksheets(“Sheet1”).Select

 

[Joe4]

Thanks. I've moved it now but am getting a "Runtime error 9 subscript out of range" error on this line....

Worksheets(“Sheet1”).Select

VBA is very picking and does not like slanted or curvy double-quote marks.
Try:

Worksheets("Sheet1").Select

 

[pontiff]

VBA is very picking and does not like slanted or curvy double-quote marks.
Try:

Worksheets("Sheet1").Select

Thanks for your patience so far! I've now got the code like this (I've changed sheet name in code and sheet)

Private Sub Workbook_Open()
Worksheets("Payments").Select
x = Format(Date, "dd/mm/yyyy")
'On Error Resume Next
Worksheets("Payments").Columns(2).Find(What:=x, LookIn:=xlValues).Activate
Application.Goto Selection, True


ActiveWindow.ScrollRow = Selection.Row
ActiveWindow.ScrollColumn = Selection.Column
End Sub

I'm now getting a "object variable or With block variable not set" for the line highlighted above in red.

 

[Joe4]

Are your dates in column B entered as dates or text?
Note that this calculation:

[COLOR=#333333]x = Format(Date, "dd/mm/yyyy")[/COLOR]

returns a text entry, not a date one

If your entry is really a date, try this instead:

x = Date

 

[pontiff]

Are your dates in column B entered as dates or text?
Note that this calculation:

[COLOR=#333333]x = Format(Date, "dd/mm/yyyy")[/COLOR]

returns a text entry, not a date one

If your entry is really a date, try this instead:

x = Date

That's it! Thank you very much. Many hours of scrolling time saved!
Thanks to all for the replies.

 

[Joe4]

You are welcome!
Glad we could help.