If sheet title is NOT this Monday's date then execute code

[bujubenji]

Hi all,

I've tried out a few approaches and haven't been able to solve this issue so I defer to your knowledge.

I have a number of worksheets which are titled as the week commencing date in the following format "dd.mm.yy", in other words, each sheet's title corresponds to the date of each week's Monday.

Within each sheet, there are a number of cells which reference a list of employees on another sheet called 'Lists'- (this is a mixture of single cell references regarding to work pattern, and named ranges relating to staff within multiple departments). The trouble arrives when I want to make changes to the info held on the 'Lists' sheet as this changes all references throughout each week's sheet- I only want it to change the current week and any subsequent weeks. e.g. on Lists we have the following:

Employee 1 FT
Employee 2 PT
Employee 3 FT

Each of the week's sheets records their performance. Let's say Employee 3 quits, so I want to remove him from the Lists page but DO NOT want to lose historic data of his performance. The only solution I have thought up, is converting the previous sheets to values as opposed to formulas by copying and pasting with VBA- something to this effect:

Dim Ws As Worksheet

For Each Ws In ThisWorkbook.Worksheets
If Ws.Name <> "Performance" And Ws.Name <> "Template" And Ws.Name <> "14.08.23" And Ws.Name <> "Lists" And Ws.Name <> "Pivot Tables" And Ws.Name <> "Place" Then
With Ws.UsedRange
.Copy
.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False


End With
End If
Next Ws
End Sub

I have separate piece of code as a workbook open event which identifies the current week and automatically opens upon that sheet:

Private Sub Workbook_open()

Dim Ws As Worksheet

For Each Ws In ActiveWorkbook.Worksheets
If Ws.Name = Format(DateAdd("d", 1 - Weekday(Date, vbMonday), Date), "dd.mm.yy") Then
Ws.Activate
ActiveSheet.Range("A1").Select
Exit Sub
End If
Next Ws

MsgBox Format(DateAdd("d", 1 - Weekday(Date, vbMonday), Date), "dd.mm.yy") & " worksheet not found" & _
" in this workbook.", vbInformation, "Warning"

End Sub

I attempted to blend the two in the following way so that I don't have to hardcode the current week sheet's name:

For Each Ws In ThisWorkbook.Worksheets
If Ws.Name <> Format(DateAdd("d", 1 - Weekday(Date, vbMonday), Date), "dd.mm.yy") And Ws.Name <> "Performance" And Ws.Name <> "Template" And Ws.Name <> "14.08.23" And Ws.Name <> "Lists" And Ws.Name <> "Pivot Tables" And Ws.Name <> "Place" Then
With Ws.UsedRange

However, this is not working at all- I tried substituting the Format line with the following two options, however, neither worked

If InStr(1, Ws.Name, Format(DateAdd("d", 1 - Weekday(Date, vbMonday), Date), "dd.mm.yy"), vbTextCompare) > 0 Then
If Ws.Name Like Format(DateAdd("d", 1 - Weekday(Date, vbMonday), Date), "dd.mm.yy") = False Then

Do you have any suggestions for fixing the code or a more elegant solution altogether?

Many thanks

 

[ranman256]

if format(Date,"w")= vbMonday then

 

[MARK858]

Assuming that you want the current day on a Monday but the previous Monday on any other day then maybe

Format(Date - Weekday(Date, 3), "dd.mm.yy")

P.S. please use code tags rather than formatting your code in different colours, it makes your code harder to read and copy

 

[MARK858]

I've just ran

Sub vvvvv()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> Format(DateAdd("d", 1 - Weekday(Date, vbMonday), Date), "dd.mm.yy") And ws.Name <> "Performance" And ws.Name <> "Template" And ws.Name <> "Lists" And ws.Name <> "Pivot Tables" And ws.Name <> "Place" Then
            MsgBox "yes"
        Else
            MsgBox "no"
        End If
    Next
End Sub

and

Sub xxxxx()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> Format(Date - Weekday(Date, 3), "dd.mm.yy") And ws.Name <> "Performance" And ws.Name <> "Template" And ws.Name <> "Lists" And ws.Name <> "Pivot Tables" And ws.Name <> "Place" Then
            MsgBox "yes"
        Else
            MsgBox "no"
        End If
    Next
End Sub

With the 2nd sheet in my workbook having the name 14.08.23 and both codes are bringing up the "no" message box for the sheet and "yes" for all the other sheets.

Edit: also tested with a sheet name Template as well as the 14.08.23 sheet and got the expected 2 no's

Are you sure that you have no leading or trailing spaces in your sheet name?