Activating a date range

  peug417 22:48 27 Oct 2007
Locked

I have a calender type worksheet which has 10 rows per week for entry. I am looking for a way of when the worksheet opens it activates the current weeks date range automatically.

My week no's are in col "a" date range in col "b"
cell "c1" has the formula =today()

any ideas?

  VoG II 08:50 28 Oct 2007

ALT + F11 to open the Visual Basic Editor. In the Project Explorer pane, right click ThisWorkbook and select View Code. Copy and paste in

Private Sub Workbook_Open()
Dim r As Range
Set r = Sheets("Sheet1").Columns(2).Find(what:=Date)
If Not r Is Nothing Then Application.Goto reference:=r.Offset(0, -1), scroll:=True
End Sub


Close the VBE, save and close the workbook then re-open it.

  peug417 10:02 28 Oct 2007

Thanks Vog
After a bit of fiddling about I have got that to work. The next complication is the sheet does not display all dates Merely week commencing dates, so for example Week 43 would commence today 28/10/2007 and the next visible date is 4/11/2007. If anyone opens the sheet this week I need it to select the 28/10/2007.

  VoG II 14:21 28 Oct 2007

Private Sub Workbook_Open()
Dim r As Range, d As Date
d = Date - WorksheetFunction.Weekday(Date) + 1
Set r = Sheets("Sheet1").Columns(2).Find(what:=d)
If Not r Is Nothing Then Application.Goto reference:=r.Offset(0, -1), scroll:=True
End Sub

  peug417 14:58 28 Oct 2007

Thanks Vog
That did the trick and works as required.
I have another issue but will post it as a new post not to confuse the issue.

  peug417 15:08 28 Oct 2007

I have a excel worksheet which has static information in the rows 1 - 15,Then dynamic weekly information in rows 16 - 535 in steps of 10.
ie week 1 = rows 16 - 25 week 2 is 26-35 etc.
The issue I have is to be able to print the static info along with 1 weeks info. so for week 17 i would need to print rows 1 - 15 and 176 - 185. I did this sort of thing in anonther project which had a hidden sheet with the static info and the dynamic info was copied acoss.

The problem is for different users the number of colums will be different, ie some users will only have 5 cols of info some could have in excess of 150.

hope this makes sense...

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best phone camera 2017

Stunning new film posters by Hattie Stewart, Joe Cruz & more

iPad Pro 10.5in (2017) review

28 astuces pour profiter au mieux de votre iPhone