Open on this week's sheet

[lapta301]

Dear All

I have a sheet for each week of the year dated for the Monday week commencing in the format yyyy-mm-dd so that this week's sheet is 2011-09-19.

What I would like is for the cursor to be placed in cell B5 for this weeks sheet when the workbook is opened.

If the correct weekly sheet doesn't exist then a warning message should be shown.

I hope that someone will be able to assist.

Many thanks

 

[GTO]

Not well tested:

Option Explicit
    
Private Sub Workbook_Open()
Dim wksInitial As Worksheet
    
    On Error Resume Next
    Set wksInitial = ThisWorkbook.Worksheets(Format(Date - Weekday(Date, vbMonday) + 1, "yyyy-mm-dd"))
    On Error GoTo 0
    
    If Not wksInitial Is Nothing Then
        Application.Goto wksInitial.Cells(5, 2)
    Else
        MsgBox Format(Date - Weekday(Date, vbMonday) + 1, "yyyy-mm-dd") & " doesn't exist"
    End If
End Sub

 

[lapta301]

GTO

Thank you, it seems to work fine.

Could you explain what is happening here:
(Format(Date - Weekday(Date, vbMonday) + 1, "yyyy-mm-dd"))

As ever my thanks for taking the time to help.

Regards from a gloomy Southern England but at least its not raining yet.

 

[GTO]

Sub stepthru()
''Format(Date - Weekday(Date, vbMonday) + 1, "yyyy-mm-dd"))
    MsgBox Weekday(Date, vbMonday) ' if date arg supplied falls on a mon, returns 1
                                    ' ..... tue, returns 2
                                    'and so on
    
    'thus, if today's Date is a tuesday, we have tuesday's date, minus 2 (days), resulting
    ' in Sun, and then plus 1 (day) returning Monday's date.  We then just format that
    '  date to hwo you are naming the sheets.
    
End Sub

Does that help?

 

[lapta301]

GTO

Thank you for the explanation makes sense now.

Again my thanks for helping me out.

 

[GTO]

...Regards from a gloomy Southern England but at least its not raining yet.

...Thank you for the explanation makes sense now.

Again my thanks for helping me out.

You are most welcome and thank you for letting me know it helped. As the saying goes, "we are all in this together."

Hopefully it is not too gloomy. I sure would like to visit your country at some point. I suppose we all suffer from 'the grass is always greener on the other side.' Here in Phoenix Arizona, it's seldom 'gloomy', but then summers in your neck of the woods are unlikely to produce "I can actually fry an egg on the sidewalk" temps.

 

[obiron]

Time for a Mr Excel House swap board?

Anyone want to swap a house in Leamington Spa (Shakespear country) in the UK with one in Whister or Boulder in the middle of the Ski season?

 

[lapta301]

GTO

Everythings working fine except that a need for a slight modification has arisen.

Instead of placing cursor in cell B5 it needs to go in the first empty cell in the range A24:A87

Sorry about this.

Many thanks

 

[GTO]

Greetings lapta,

Maybe:

Option Explicit
    
Private Sub Workbook_Open()
Dim wksInitial      As Worksheet
Dim rngCheckBlank   As Range
Dim lRowInRange     As Long
    
    On Error Resume Next
    Set wksInitial = ThisWorkbook.Worksheets(Format(Date - Weekday(Date, vbMonday) + 1, "yyyy-mm-dd"))
    On Error GoTo 0
    
    'Instead of placing cursor in cell B5 it needs to go in the first empty cell in the range A24:A87
    If Not wksInitial Is Nothing Then
        Set rngCheckBlank = wksInitial.Range("A24:A87")
        
        '// Evaluate is expensive, not a loop though, so maybe...   //
        On Error Resume Next
        lRowInRange = Evaluate("MATCH(TRUE,'" & rngCheckBlank.Parent.Name & "'!" & rngCheckBlank.Address & "="""",0)")
        On Error GoTo 0
        
        If lRowInRange Then
            Application.Goto wksInitial.Cells(23 + lRowInRange, 1)
        Else
            Application.Goto wksInitial.Cells(5, 2)
        End If
    Else
        MsgBox Format(Date - Weekday(Date, vbMonday) + 1, "yyyy-mm-dd") & " doesn't exist"
    End If
End Sub

 

[lapta301]

GTO

Works a treat thank you so much.

Again my grateful thanks for your help.

Steve