Changing 2 worksheet name at the same time

[FlipEternalX]

Hi Everyone, I have code here that changing a worksheet name. Now, I want to put "Cleaners" 1 and "Cleaners 2" for 2 worksheets and resetting/getting back to the first one after reaching the last sheet. ( not based on color)

Private Sub Workbook_Open()
    Dim OriginalDate As Date
    Dim Weeks As Integer
    Dim SheetNum As Integer
    Dim sh As Worksheet
    
    
    OriginalDate = #12/31/2023#
    Weeks = DateDiff("ww", OriginalDate, Now)
    SheetNum = Weeks Mod Sheets.Count + 1
       
    If InStr(1, Sheets(SheetNum).Name, "Cleaners") < 1 Then

        '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

Here is the current output of the code.

I want a output like this.

Then it will go back to Jane (Cleaners 1) & Aether (Cleaners 2) again after the week have been passed for Justin, and Clare.

 

[Micron]

Seeing what you start out with would help because you might be going about this in a convoluted manner. I wonder if all you need (aside from protecting/unprotecting is
Sheets(SheetNum).Name = Sheets(SheetNum).Name & "(Cleaners " & i & ")" where you increment i in a loop. as long as the sheet number fits the requirement. I cannot recall if sheet number references get messed up if you move sheets around or insert them, so that might turn out to be an issue at some point

 

[FlipEternalX]

Seeing what you start out with would help because you might be going about this in a convoluted manner. I wonder if all you need (aside from protecting/unprotecting is
Sheets(SheetNum).Name = Sheets(SheetNum).Name & "(Cleaners " & i & ")" where you increment i in a loop. as long as the sheet number fits the requirement. I cannot recall if sheet number references get messed up if you move sheets around or insert them, so that might turn out to be an issue at some point

Hello. Where should I put this one?

 

[Micron]

Where should I put this one?

I don't know where or even if that's a valid suggestion because I said

Seeing what you start out with would help

 

[FlipEternalX]

bump

 

[Micron]

Why are you just bumping your thread instead of providing the information that was asked for?

 

[FlipEternalX]

Why are you just bumping your thread instead of providing the information that was asked for?

Hi Sorry, I didn't understand what you are saying on your last comment. I already get the solution. The only problem is that they are overlapping each other as I said on my last part of my post.

 

[Micron]

OK, you're still not showing what you have to begin with so I will wish you good luck with this one.