Need help with VBA formatting of date

ajc623

Board Regular
Joined
Nov 8, 2013
Messages
50
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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

jsotola

Well-known Member
Joined
Nov 15, 2013
Messages
524
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

Code:
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

Well-known Member
Joined
Mar 13, 2010
Messages
523
You forgot about the next work day. This should work.

Code:
    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)
 

Watch MrExcel Video

Forum statistics

Threads
1,100,199
Messages
5,473,114
Members
406,845
Latest member
JohnR123

This Week's Hot Topics

Top