Need help with VBA to select cell containing current week

sparkytech

New Member
Joined
Mar 6, 2018
Messages
37
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!
 

Attachments

  • Week Beginning.jpg
    Week Beginning.jpg
    66.1 KB · Views: 5

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,608
Office Version
  1. 365
Platform
  1. Windows
One way would be to use something like this in the workbook module.
VBA Code:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range("C11:BC11")(Evaluate("MATCH(TODAY(),C11:BC11)")).Select
End Sub
 
Solution

sparkytech

New Member
Joined
Mar 6, 2018
Messages
37
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

Well-known Member
Joined
Dec 30, 2008
Messages
14,608
Office Version
  1. 365
Platform
  1. Windows
Like this,
Excel Formula:
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

New Member
Joined
Mar 6, 2018
Messages
37
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!

VBA Code:
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
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,610
Messages
5,765,412
Members
425,285
Latest member
andypandypoo

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
Top