Auto open vba select column

[excellence]

I have this vba to auto open and find today's date from column A and select the cell next to it in column B


Private Sub Workbook_Open()
Dim myDate As Variant, rng As Range
Set rng = Range("A1:A68")
Set myDate = rng.Find(What:=Int(Date), LookIn:=xlFormulas)
Cells(myDate.Row, myDate.Column + 1).Select

End Sub
I have dates in column spanning todays date
I've got it in sheet 1 but doesn't seem to work

Thanks for any help

 

[JerrExcel]

Hi there,

It doesn't to be any issue in your code.

However, if you have multipler sheets in that template, you might need to specify your sheet name / sheet number like below:

Private Sub Workbook_Open()
Dim myDate As Variant, rng As Range
Set rng = Worksheets(1).Range("A1:A68")
Set myDate = rng.Find(What:=Int(Date), LookIn:=xlFormulas)
Worksheets(1).Activate
Worksheets(1).Cells(myDate.Row, myDate.Column + 1).Select

End Sub

Remember to put this code inside "Workbook", not in a particular module:

 

[excellence]

Hi there,

It doesn't to be any issue in your code.

However, if you have multipler sheets in that template, you might need to specify your sheet name / sheet number like below:

Private Sub Workbook_Open()
Dim myDate As Variant, rng As Range
Set rng = Worksheets(1).Range("A1:A68")
Set myDate = rng.Find(What:=Int(Date), LookIn:=xlFormulas)
Worksheets(1).Activate
Worksheets(1).Cells(myDate.Row, myDate.Column + 1).Select

End Sub

Remember to put this code inside "Workbook", not in a particular module:
View attachment 110480

Hi
I put it in this workbook and still no luck. Copied and pasted your vba
On left side looks like yours VBA >MS Excel Objects>Sheet1 and This Workbook under it

 

[Cubist]

Try:

Private Sub Workbook_Open()
    Dim myDate As Range, rng As Range
    Set rng = Sheets("Sheet1").Range("A1:A68") 'change sheet name as needed
    Set myDate = rng.Find(What:=Int(Date), LookIn:=xlValues)
    If Not myDate Is Nothing Then
        myDate.Offset(0, 1).Select 
        MsgBox "Today's date not found in the specified range.", vbExclamation
    End If
End Sub

 

[excellence]

the right side, however has the name of the macro as the title with General and Declarations underneath it

 

[excellence]

Private Sub Workbook_Open() Dim myDate As Range, rng As Range Set rng = Sheets("Sheet1").Range("A1:A68") 'change sheet name as needed Set myDate = rng.Find(What:=Int(Date), LookIn:=xlValues) If Not myDate Is Nothing Then myDate.Offset(0, 1).Select MsgBox "Today's date not found in the specified range.", vbExclamation End If End Sub

Did not work. I must have something in the wrong place

 

[Cubist]

How are your dates formatted? Are they from a formula or value?

 

[excellence]

Hi there,

It doesn't to be any issue in your code.

However, if you have multipler sheets in that template, you might need to specify your sheet name / sheet number like below:

Private Sub Workbook_Open()
Dim myDate As Variant, rng As Range
Set rng = Worksheets(1).Range("A1:A68")
Set myDate = rng.Find(What:=Int(Date), LookIn:=xlFormulas)
Worksheets(1).Activate
Worksheets(1).Cells(myDate.Row, myDate.Column + 1).Select

End Sub

Remember to put this code inside "Workbook", not in a particular module:
View attachment 110480

Got it
So many thanks. I thought I got it in workbook but hadn't

 

[JerrExcel]

the right side, however has the name of the macro as the title with General and Declarations underneath it

Yes, initially when you select "Thisworkbook", you will see "General" and "Declaration".

Just make sure to select dropdown "Workbook"

and select "Open":

Then insert the codes.

Thanks to Cubist
which provided a better code. (you missed out "else" in the if condition)
Here is my code update:

Private Sub Workbook_Open()
    Dim myDate As Range, rng As Range
    Set rng = Sheets("Sheet1").Range("A1:A68") 'change sheet name as needed
    Set myDate = rng.Find(What:=Int(Date), LookIn:=xlValues)
    If Not myDate Is Nothing Then
        Sheets("Sheet1").Activate
        myDate.Offset(0, 1).Select
        Else
        MsgBox "Today's date not found in the specified range.", vbExclamation
    End If
End Sub