Automatically renaming worksheets to weekdays

[ginginio]

I have written this:

Sub Finished()
Application.ScreenUpdating = False
MsgBox ("IMPORTANT: rename the 'NEW' worksheet to the day of the
week.")
Sheets("TODAY").Select
Sheets("TODAY").Copy After:=ActiveSheet
Sheets("TODAY (2)").Select
Sheets("TODAY (2)").Name = "NEW"
Sheets("TODAY").Select
Rows("4:300").Select
Selection.ClearContents
Sheets("NEW").Select
ActiveSheet.Shapes("Button 1").Select
Selection.delete
Sheets("TODAY").Select
Range("B4").Select
Sheets("NEW").Select
Range("B4").Select
End Sub

....which, as you can see, takes all the info on the "TODAY" worksheet
and copies it to a new sheet called "NEW", and then does a little
housekeeping. (The last four lines are there to get rid of the
selection of 300 rows which I think looks a bit messy. Pure vanity
Rather than have the user rename the worksheet "NEW" manually every
time, is there a way of having Excel rename the sheet automatically to
whatever day of the week it is?
Thanks in advance.

-Chris M.

 

[Bob Phillips]

Sub Finished()
Application.ScreenUpdating = False
Sheets("TODAY").Copy After:=ActiveSheet
Sheets("TODAY (2)").Name = Format(Date, "dddd")
Sheets("TODAY").Rows("4:300").ClearContents
Sheets("NEW").Select
ActiveSheet.Shapes("Button 1").Delete
Sheets("TODAY").Select
Range("B4").Select
Sheets("NEW").Select
Range("B4").Select
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[ginginio]

Thank you very much for your reply. However, the macro now looks for a
sheet called "NEW" which doesn't exist, in line 6. Obviously the sheet
to select will have been renamed relative to what day it is, so is it
possible to select it once it has been renamed?

-Chris M.

 

[Bob Phillips]

Oops, sorry about that

Sub Finished()
Application.ScreenUpdating = False
Sheets("TODAY").Copy After:=ActiveSheet
Sheets("TODAY (2)").Name = Format(Date, "dddd")
Sheets("TODAY").Rows("4:300").ClearContents
ActiveSheet.Shapes("Button 1").Delete
Sheets("TODAY").Select
Range("B4").Select
Sheets(Format(Date, "dddd")).Select
Range("B4").Select
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[ginginio]

Oops, sorry about that

Sub Finished()
Application.ScreenUpdating = False
Sheets("TODAY").Copy After:=ActiveSheet
Sheets("TODAY (2)").Name = Format(Date, "dddd")
Sheets("TODAY").Rows("4:300").ClearContents
ActiveSheet.Shapes("Button 1").Delete
Sheets("TODAY").Select
Range("B4").Select
Sheets(Format(Date, "dddd")).Select
Range("B4").Select
End Sub

That works like a charm.

Thank you for both your time and your help.

-Chris M.