Auto Update Sheets in Scheduler file

[anuradhagrewal]

Hi
Enclosed is the Scheduler file.
I request you to kindly help me with the following by way of a macros.
1)I have added a scheduler till 25-Jun-24--30-Jun-24.
2)However I request a macros where the same worksheet mentioned above is created but with the first working day(Monday) to the last working day(Saturday). That implies the same format be copied on a fresh worksheet but the date to mentioned above in Row A be : 1-Jul-24---6-Jul-24 and so on perpetually. This should all be part of the same workbook.
3)This name that reflect in Row A should also be the name of the worksheet.
4)I also put in a color coding (I dont know how?) wherein if anything is high priority it turns RED fill and Medium Priority its Orange fill.

Kindly please help

Best Wishes

Anuradha

 

[NoSparks]

4)I also put in a color coding (I dont know how?) wherein if anything is high priority it turns RED fill and Medium Priority its Orange fill.

What in the cells would determine their priority?

 

[anuradhagrewal]

What in the cells would determine their priority?

Thanks for the reply.
If I put say (U) the cell should turn RED
(T) i.e Tomorrow it should turn GREEN
(M)i.e In a month from now it should turn BLUE

 

[NoSparks]

Try making a copy of one of your sheets, rename it TEMPLATE
Apply Conditional Formatting to TEMPLATE for the colors >
select cells A3:G37
click Conditional Formatting on the Home ribbon
Highlight Cell Rules > Text that contains....
make it what you want, 3 times, once for each color.

Copy this macro into a standard code module

Sub anuradha_CreateSheets()
    Dim i As Long
    Dim wkStart As Date
    Dim wkEnd As Date
    Dim shtname As String
    Dim AlreadyHaveSheet As Worksheet
    
' first Monday of the year
wkStart = DateSerial(2024, 1, 8) - Weekday(DateSerial(2024, 1, 6))
' first Saturday of the year
wkEnd = wkStart + 5

For i = 0 To 52     'may produce extra sheet at end
    ' name of weekly sheet
    shtname = Format(wkStart + (i * 7), "dd-mmm-yy") & "--" & Format(wkEnd + (i * 7), "dd-mmm-yy")
    
    ' check if already exists
    On Error Resume Next    'don't show error when sheet doesn't exist
    Set AlreadyHaveSheet = Sheets(shtname)
    On Error GoTo 0         're-enable error notifications
    
    If AlreadyHaveSheet Is Nothing Then     'sheet doesn't exist
        Sheets("TEMPLATE").Copy After:=Sheets(Sheets.Count)
        With ActiveSheet
            .Name = shtname
            .Cells(1, 1) = shtname
        End With
    End If
    
    ' reset things for next loop
    shtname = ""
    Set AlreadyHaveSheet = Nothing
    
Next i

End Sub

Hope that helps

 

[anuradhagrewal]

Try making a copy of one of your sheets, rename it TEMPLATE
Apply Conditional Formatting to TEMPLATE for the colors >
select cells A3:G37
click Conditional Formatting on the Home ribbon
Highlight Cell Rules > Text that contains....
make it what you want, 3 times, once for each color.

Copy this macro into a standard code module

Sub anuradha_CreateSheets()
    Dim i As Long
    Dim wkStart As Date
    Dim wkEnd As Date
    Dim shtname As String
    Dim AlreadyHaveSheet As Worksheet
  
' first Monday of the year
wkStart = DateSerial(2024, 1, 8) - Weekday(DateSerial(2024, 1, 6))
' first Saturday of the year
wkEnd = wkStart + 5

For i = 0 To 52     'may produce extra sheet at end
    ' name of weekly sheet
    shtname = Format(wkStart + (i * 7), "dd-mmm-yy") & "--" & Format(wkEnd + (i * 7), "dd-mmm-yy")
  
    ' check if already exists
    On Error Resume Next    'don't show error when sheet doesn't exist
    Set AlreadyHaveSheet = Sheets(shtname)
    On Error GoTo 0         're-enable error notifications
  
    If AlreadyHaveSheet Is Nothing Then     'sheet doesn't exist
        Sheets("TEMPLATE").Copy After:=Sheets(Sheets.Count)
        With ActiveSheet
            .Name = shtname
            .Cells(1, 1) = shtname
        End With
    End If
  
    ' reset things for next loop
    shtname = ""
    Set AlreadyHaveSheet = Nothing
  
Next i

End Sub

Hope that helps

Hi
It didn't work

 

[NoSparks]

It didn't work

It did for me, here's my file

 

[anuradhagrewal]

It did for me, here's my file

Thanks It does work