Using vba to go to column with current week

DracoDormiens

New Member
Joined
Aug 21, 2019
Messages
3
Hi there,Sorry if I am posting a new thread and it has already been answered else where. I have looked and looked, but no luck so far.I have a time & resource planning tool which has fixed data on columns A to G (name , activity , priority etc). From H on wards are week to week columns.What I am trying to do is get the current week to pop up first and scroll all the way to the left. This means that past planning information is not stuck between the fixed columns (A to G) and the current and future relevant weeks. So I thought I managed to crack this by using the following VBA code:Sub gotoDate ()Dim c As RangeDim d As Dated = Date For Each c In Range("H4:AT4") If c = d Then Application.Goto c, True End If Next cEnd SubThis worked/ works beautifully if we are on the actual date today that is shown on the sheet. So for example, my week to week inputs are 19-Aug-19, 26-Aug-19, 02-Sep-19 and so on. The sheet will only go to the 19-Aug-19 week column if it is the 19-Aug-19. What I am looking to do is essentially what I have, but add a further range for that week instead. Can anyone help me out? Thanks,D
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Please use CODE tag around vba code. Now it's hard to read really.
 
Upvote 0
Hi & welcome to MrExcel.
How about
Code:
d = Date - Weekday(Date, vbMonday) + 1
 
Upvote 0
Hi there, First Noob move on my first post. hope this works.

Sorry if I am posting a new thread and it has already been answered else where. I have looked and looked, but no luck so far.I have a time & resource planning tool which has fixed data on columns A to G (name , activity , priority etc). From H on wards are week to week columns.What I am trying to do is get the current week to pop up first and scroll all the way to the left.
This means that past planning information is not stuck between the fixed columns (A to G) and the current and future relevant weeks. So I thought I managed to crack this by using the following VBA

Code:
Sub gotoDate ()
Dim c As Range
Dim d As  Date
d = Date
    For Each c In Range("H4:AT4")        
If c = d Then             
Application.Goto c, True        
End If    
Next c
End Sub

This worked/ works beautifully if we are on the actual date today that is shown on the sheet. So for example, my week to week inputs are 19-Aug-19, 26-Aug-19, 02-Sep-19 and so on. The sheet will only go to the 19-Aug-19 week column if it is the 19-Aug-19. What I am looking to do is essentially what I have, but add a further range for that week instead. Can anyone help me out? Thanks,D
 
Upvote 0
You're welcome & thanks for the feedback.
This part
Code:
Weekday(Date, vbMonday)
returns the day of the week starting with Monday as 1, so for today it returns 3
That is then subtracted from today's date to give 18/8/19 & then we add 1 to bring it back to Mondays date, the 19th
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

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
Back
Top