Assigning a "cleaners" word for every worksheet every 7 days and moving it to another person after a week.

[FlipEternalX]

Hello everyone, I hope you are doing great.

I want to add a word "Cleaners" for every worksheet on the workbook.

So for example, Today is Monday. So the "Cleaners" word will be added to Jane so the name of the worksheet of Jane will be Jane (Cleaners)

And then after that, I also want to move the "Cleaners" word after 1 week, so after 1 week the word Cleaners will be put to "Jayvee" then the Cleaners word on Jane will be removed.

Then same with Lorie.

I hope you help me with this problem.

Thank you all.

 

[FlipEternalX]

Yes, the script must be rerun to move the word. However, . . .


it will automatically be rerun whenever you open the workbook, even if you don't open it for a week. In fact, it reruns every time you open the workbook, so multiple times per day? Multiple days in the same week? It just doesn't appear to change anything because the same sheet is reset with Cleaners.

Now that I think about it, the code I originally provided changes the sheet names even if they don't need it (they already were changed this week). In this code, it checks to see if the designated sheet needs changing. If not, nothing happens. If so, then the sheets are changed.

Private Sub Workbook_Open()
    Dim OriginalDate As Date
    Dim Weeks As Integer
    Dim SheetNum As Integer
    Dim sh As Worksheet
   
    'Change this date to be the Sunday of the first week (the first sheet to have "Cleaners" added to it)
    OriginalDate = #12/24/2023#
    Weeks = DateDiff("ww", OriginalDate, Now)
    SheetNum = Weeks Mod Sheets.Count + 1
   
    If InStr(1, Sheets(SheetNum).Name, "Cleaners") < 1 Then

        'Change the password to the workbook protection password
        ThisWorkbook.Unprotect "PA$$WORD"

        For Each sh In Sheets
            sh.Name = Replace(sh.Name, " (Cleaners)", "")
        Next sh
        Sheets(SheetNum).Name = Sheets(SheetNum).Name & " (Cleaners)"
        ThisWorkbook.Protect Structure:=True, Windows:=False
    End If
End Sub

To see what the code would do next week, pretend that you started this last week and change the date in the code to Dec. 24. Close and reopen the workbook. Since today is the "second" week now, the second sheet should now have Cleaners. Then, change the date back to today.


The way the code is set up now is strictly based on the number of sheets and their order from left to right. The first sheet gets "Cleaners" today if this is week 0, the second if week 1, etc. This makes it easy if people come and go because you can simply remove or add sheets in the correct order, and the sheet name doesn't matter.

To start having 2 sheets updated requires the code to be changed. It seems easy enough to do if you base it again on the order of the sheets. If you have an even number of sheets and order the cleaners so that the two for each week are next to each other in the sheet order, the code could be changed to use sheets 1-2 the first week, 3-4 the second week, and so on. If you want any other sort of order or there is an odd number of people, then the code would need to get more busy.

Hi, I understand now. Thanks for answering. Would it be possible to add a Cleaners 1 and Cleaners 2 at the same for 2 different sheets and the logic still continue after and moving it to another person/worksheet time at the same week?

 

[FlipEternalX]

Hi, I understand now. Thanks for answering. Would it be possible to add a Cleaners 1 and Cleaners 2 at the same for 2 different sheets and the logic still continue after and moving it to another person/worksheet time at the same week?

One more thing is the 2 sheets are far to each other.

 

[shknbk2]

One more thing is the 2 sheets are far to each other.

At this point, you will need to have an extra configuration sheet set up to identify the order of the cleaning personnel. Then, the code will use that sheet to know how to assign the "Cleaner" titles. Maybe column A can be the list of Cleaner 1, and column B can be the list of Cleaner 2.

 

[FlipEternalX]

Yes, the script must be rerun to move the word. However, . . .


it will automatically be rerun whenever you open the workbook, even if you don't open it for a week. In fact, it reruns every time you open the workbook, so multiple times per day? Multiple days in the same week? It just doesn't appear to change anything because the same sheet is reset with Cleaners.

Now that I think about it, the code I originally provided changes the sheet names even if they don't need it (they already were changed this week). In this code, it checks to see if the designated sheet needs changing. If not, nothing happens. If so, then the sheets are changed.

Private Sub Workbook_Open()
    Dim OriginalDate As Date
    Dim Weeks As Integer
    Dim SheetNum As Integer
    Dim sh As Worksheet
   
    'Change this date to be the Sunday of the first week (the first sheet to have "Cleaners" added to it)
    OriginalDate = #12/24/2023#
    Weeks = DateDiff("ww", OriginalDate, Now)
    SheetNum = Weeks Mod Sheets.Count + 1
   
    If InStr(1, Sheets(SheetNum).Name, "Cleaners") < 1 Then

        'Change the password to the workbook protection password
        ThisWorkbook.Unprotect "PA$$WORD"

        For Each sh In Sheets
            sh.Name = Replace(sh.Name, " (Cleaners)", "")
        Next sh
        Sheets(SheetNum).Name = Sheets(SheetNum).Name & " (Cleaners)"
        ThisWorkbook.Protect Structure:=True, Windows:=False
    End If
End Sub

To see what the code would do next week, pretend that you started this last week and change the date in the code to Dec. 24. Close and reopen the workbook. Since today is the "second" week now, the second sheet should now have Cleaners. Then, change the date back to today.


The way the code is set up now is strictly based on the number of sheets and their order from left to right. The first sheet gets "Cleaners" today if this is week 0, the second if week 1, etc. This makes it easy if people come and go because you can simply remove or add sheets in the correct order, and the sheet name doesn't matter.

To start having 2 sheets updated requires the code to be changed. It seems easy enough to do if you base it again on the order of the sheets. If you have an even number of sheets and order the cleaners so that the two for each week are next to each other in the sheet order, the code could be changed to use sheets 1-2 the first week, 3-4 the second week, and so on. If you want any other sort of order or there is an odd number of people, then the code would need to get more busy.

Hello, how do I add Cleaners 1 and Cleaners 2 at the same time every week?

 

[FlipEternalX]

Yes, the script must be rerun to move the word. However, . . .


it will automatically be rerun whenever you open the workbook, even if you don't open it for a week. In fact, it reruns every time you open the workbook, so multiple times per day? Multiple days in the same week? It just doesn't appear to change anything because the same sheet is reset with Cleaners.

Now that I think about it, the code I originally provided changes the sheet names even if they don't need it (they already were changed this week). In this code, it checks to see if the designated sheet needs changing. If not, nothing happens. If so, then the sheets are changed.

Private Sub Workbook_Open()
    Dim OriginalDate As Date
    Dim Weeks As Integer
    Dim SheetNum As Integer
    Dim sh As Worksheet
   
    'Change this date to be the Sunday of the first week (the first sheet to have "Cleaners" added to it)
    OriginalDate = #12/24/2023#
    Weeks = DateDiff("ww", OriginalDate, Now)
    SheetNum = Weeks Mod Sheets.Count + 1
   
    If InStr(1, Sheets(SheetNum).Name, "Cleaners") < 1 Then

        'Change the password to the workbook protection password
        ThisWorkbook.Unprotect "PA$$WORD"

        For Each sh In Sheets
            sh.Name = Replace(sh.Name, " (Cleaners)", "")
        Next sh
        Sheets(SheetNum).Name = Sheets(SheetNum).Name & " (Cleaners)"
        ThisWorkbook.Protect Structure:=True, Windows:=False
    End If
End Sub

To see what the code would do next week, pretend that you started this last week and change the date in the code to Dec. 24. Close and reopen the workbook. Since today is the "second" week now, the second sheet should now have Cleaners. Then, change the date back to today.


The way the code is set up now is strictly based on the number of sheets and their order from left to right. The first sheet gets "Cleaners" today if this is week 0, the second if week 1, etc. This makes it easy if people come and go because you can simply remove or add sheets in the correct order, and the sheet name doesn't matter.

To start having 2 sheets updated requires the code to be changed. It seems easy enough to do if you base it again on the order of the sheets. If you have an even number of sheets and order the cleaners so that the two for each week are next to each other in the sheet order, the code could be changed to use sheets 1-2 the first week, 3-4 the second week, and so on. If you want any other sort of order or there is an odd number of people, then the code would need to get more busy.

I tried to run this code and I set the OriginalDate to 12/24/2023, so the output should be Jayvee (Cleaners) but I still received the Jane (Cleaners), why is that so? can you explain? Thanks.

Private Sub Workbook_Open()
    Dim OriginalDate As Date
    Dim Weeks As Integer
    Dim SheetNum As Integer
    Dim sh As Worksheet
    
    'Change this date to be the Sunday of the first week (the first sheet to have "Cleaners" added to it)
    OriginalDate = 12 / 24 / 2023
    Weeks = DateDiff("ww", OriginalDate, Now)
    SheetNum = Weeks Mod Sheets.Count + 1
    
    If InStr(1, Sheets(SheetNum).Name, "Cleaners") < 1 Then

        'Change the password to the workbook protection password
        ThisWorkbook.Unprotect "testpassword"

        For Each sh In Sheets
            sh.Name = Replace(sh.Name, " (Cleaners)", "")
        Next sh
        Sheets(SheetNum).Name = Sheets(SheetNum).Name & " (Cleaners)"
        ThisWorkbook.Protect Structure:=True, Windows:=False
    End If
End Sub

 

[shknbk2]

You have to put the date between Hash tags. That's the way to define a date. So,

OriginalDate = #12/24/2023#

What happens with your code is the date is 12 divided by 24 divided by 2023, which is 0.0002471576866040534, which, believe it or not, is an actual date that Excel recognizes. The date is 1/0/1900 12:00:21 AM.

 

[FlipEternalX]

You have to put the date between Hash tags. That's the way to define a date. So,

OriginalDate = #12/24/2023#

What happens with your code is the date is 12 divided by 24 divided by 2023, which is 0.0002471576866040534, which, believe it or not, is an actual date that Excel recognizes. The date is 1/0/1900 12:00:21 AM.

Woah, this one work now. Thanks for your help. The only problem I am thinking is the 2 cleaners with different sheets assigned.