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]

Glad to help.

 

[lapta301]

GTO

Not sure if you will pick this up but I have a request for an addition to this code.

This code works absolutely perfectly but I have a special sheet named control that I would like moved to the left of whatever the current weeks sheet is.

So when I open the workbook today the code will find the weekly sheet dated 2012-06-18 and what I would like is for the control sheet to be moved from wherever it is to the left of the sheet named 2012-06-18

I hope that this makes sense and regardless of whether or not this can be done the original code is beautiful and makes daily life much easier when opening up the many workbooks that I have with weekly sheets.

Regards from a sunny Southern England

 

[VoG]

Try

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
        Worksheets("control").Move before:=ActiveSheet
    Else
        MsgBox Format(Date - Weekday(Date, vbMonday) + 1, "yyyy-mm-dd") & " doesn't exist"
    End If
End Sub

 

[lapta301]

Hi Peter

We meet again

Sort of works other than the Control sheet has now become the active sheet rather than the weekly sheet.

 

[VoG]

Then maybe

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
        Worksheets("control").Move before:=wksInitial
        
        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]

Peter

That's perfect thank you very much.

Regards

Steve