making specific dates in VBA generic so it can be applied continuously

[Enzo_Matrix]

I am creating an on-time delivery report and this is the code for one of the steps in my process

Sub NewDay()'
' NewDay Macro
' Button that initiates new day set up for on-time delivery reports
'
' Keyboard Shortcut: Ctrl+Shift+A
'
    Sheets("Feb 23, 2018").Select
    Sheets("Feb 23, 2018").Copy Before:=Sheets(4)
    Sheets("Feb 23, 2018 (2)").Select
    Sheets("Feb 23, 2018 (2)").Name = "Feb 24, 2018"
    Sheets("Feb 23, 2018").Select
    Range("B2").Select
End Sub

I am wondering if there is a way to make the dates in this code change automatically for today's current date
Would changing

Sheets("Feb 23, 2018 (2)").Name = "Feb 24, 2018"
' to Sheets("Feb 23, 2018 (2)").Name = Today()

achieve my goal of having the name of the sheets change on their own to the next day in the sequence?

 

[jproffer]

Looks like it would. Try manually changing the last sheet to something other than today's date and try it out, but it looks like it would, to me.

 

[Fluff]

Try

Sub NewDay() '
' NewDay Macro
' Button that initiates new day set up for on-time delivery reports
'
' Keyboard Shortcut: Ctrl+Shift+A
'
   Sheets(Format(Date, "mmm dd, yyyy")).Copy Before:=Sheets(4)
   ActiveSheet.Name = Format(Date + 1, "mmm dd, yyyy")
   Range("B2").Select
End Sub

 

[Enzo_Matrix]

modified code to:

Sub NewDay()'
' NewDay Macro
' Button that initiates new day set up for on-time delivery reports
'
' Keyboard Shortcut: Ctrl+Shift+A
'
    Sheets(3).Select
    Sheets(3).Copy Before:=Sheets(4)
    Sheets(4).Select
    Sheets(4).Name = Today()
    Sheets(3).Select
    Range("B2").Select
End Sub

ran it and came up with error message: "Compile error: Sub or Function not defined". It then highlights the 'Today()' text as well as 'Sub NewDay()'.

 

[Enzo_Matrix]

thanks for the advice Fluff,

Copied and pasted your code and came up with and error message:
Run-time error '9'"
Subscript out of range

 

[Fluff]

ok, do you have a sheet called Feb 23, 2018 & do you have 4 sheets in your workbook?

 

[Enzo_Matrix]

I do have a sheet called Feb 23, 2018 and the only time there is a 4th sheet is when Feb 23, 2018 is duplicated and the date is changed to the next day (only weekdays are counted).

In the second Code, I changed the names in the sheets to 3,4 as they will continuously be in the same positions of the workbook

 

[Fluff]

Try this

Sub NewDay() '
' NewDay Macro
' Button that initiates new day set up for on-time delivery reports
'
' Keyboard Shortcut: Ctrl+Shift+A
'
   Sheets(Format(Date, "mmm dd, yyyy")).Copy Before:=Sheets(Sheets.Count)
   ActiveSheet.Name = Format(Date + 1, "mmm dd, yyyy")
   Range("B2").Select
End Sub

It will put the new sheet before the last sheet in the book

 

[Enzo_Matrix]

Thank you Fluffy for that

It does work however the new sheets that are populated are named:
Feb 24, 2018
Feb 23, 2018 (2)
Feb 23, 2018 (3)......

 

[Fluff]

That's because you've already got a sheet for the 24th & you cannot have 2 sheets with the same name.