Need help with VBA formatting of date

[ajc623]

Fairly new to using the more advanced features in excel and need help. What I have is a workbook with the daily schedule for several employees and at the end of each day I print the schedule in 3 different formats and then want to save the sheet by copying in and renaming it with the next work day as the name in mmddyy format. Below is what I have which works great except I can figure out how to have the date be the next workday (M-F) and not just today. Thanks

Sheets("Today").Copy Before:=Sheets(9)
Sheets("Today (2)").Select
Sheets("Today (2)").Name = Format(Date, mmddyy)

Andrew

 

[jsotola]

you almost had it

no need to select a worksheet to rename it

as a sidenote : same with cells, no need to select cells to work with them

Sub test()


    Sheets("Today").Copy Before:=Sheets(9)


    Sheets("Today (2)").Name = Format(Date[COLOR=#ff0000] + 1[/COLOR], [COLOR=#ff0000]"[/COLOR]mmddyy[COLOR=#ff0000]"[/COLOR])


End Sub

 

[djreiswig]

You forgot about the next work day. This should work.

    NextWorkDay = Date + 1
    'increment NextWorkDay if it is a weekend
    If Weekday(NextWorkDay, vbMonday) > 5 Then
        While Weekday(NextWorkDay, vbMonday) > 5
            NextWorkDay = NextWorkDay + 1
        Wend
    End If
    Sheets("Today").Copy Before:=Sheets(9)
    Sheets("Today (2)").Name = Format(NextWorkDay, mmddyy)

 

[ajc623]

Thanks for the help that works great!