Need help with VBA to select cell containing current week

[sparkytech]

My Excel workbook contains multiple sheets which are all the same template other than the data in the cells. Each sheet has dates in B:11 to BC:11 which are the dates of each Monday for the year. I need to select (jump to) the cell containing the current week whenever each sheet is opened. Example: Today is 02.16.2021, so when I open each sheet it would need to jump to cell I:11.

I am still learning VBA and have no clue how to do this. Any help would be greatly appreciated!

 

[jasonb75]

One way would be to use something like this in the workbook module.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range("C11:BC11")(Evaluate("MATCH(TODAY(),C11:BC11)")).Select
End Sub

 

[sparkytech]

That works great, thanks! One more question... I have an "Admin" sheet that I want to exclude from this function. Is there a way to do that?

 

[jasonb75]

Like this,

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name <> "Admin" Then Range("C11:BC11")(Evaluate("MATCH(TODAY(),C11:BC11)")).Select
End Sub

If you wanted to include / exclude multiple sheets then Select Case would be easier to manage (remember that it is case sensitive as well).

 

[sparkytech]

Once again, much thanks! This works great and saved me a lot of time reverse engineering. I added a line to make it scroll the current week column to the right of column "A" which is frozen. Final code below if it helps anyone else. Thanks!

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name <> "Admin" Then Range("C11:BC11")(Evaluate("MATCH(TODAY(),C11:BC11)")).Select
ActiveWindow.ScrollColumn = ActiveCell.Column
End Sub