extra command

[jkf]

is it possible for excel to move on to the day using the pc clock,

i have a doc that uses sheets for each day of week as in


Sub ADDSHEET()
For G = 31 To 1 Step -1
Sheets.Add.Name = G & ".01.08"
Next G
End Sub

so would it be possoible to add a extra command so it would open the
corresponding sheet dependant on date

hope this makes sense

tia

JKF

 

[Gord Dibben]

Sub pick_today()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = Format(Now, "dd.mm.yy") Then
ws.Select
End If
Next
End Sub

You could place the code into a workbook_open event if you chose to.

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = Format(Now, "dd.mm.yy") Then
ws.Select
End If
Next
End Sub


Gord Dibben MS Excel MVP

 

[jkf]

thank you worked a treat

many thanks

JKF

Sub pick_today()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = Format(Now, "dd.mm.yy") Then
ws.Select
End If
Next
End Sub

You could place the code into a workbook_open event if you chose to.

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = Format(Now, "dd.mm.yy") Then
ws.Select
End If
Next
End Sub


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Thanks for the feedback.

Gord

thank you worked a treat

many thanks

JKF

 

[Dave Peterson]

Another option based on Gord's code:

Private Sub Workbook_Open()
Dim ws As Worksheet
set ws = nothing
on error resume next
set ws = worksheets(Format(Date, "dd.mm.yy")) 'I like date, Gord likes Now!
on error goto 0
if ws is nothing then
beep 'no sheet by that name
else
application.goto ws.range("a1"), scroll:=true
end if
End Sub

ps. If you use Gord's code, you may want to add:
Exit For
after the
ws.select
line

There's no reason to keep looking.

 

[Gord Dibben]

Thanks for the input Dave.

As usual, way ahead of me<g>

I like posting my amateur code so's I learn how to do it correctly.

BUT...........I don't seem to be too swift on the learning part.


Gord

 

[Dave Peterson]

You're welcome.

I know what you mean! (Where's Tom!?!)