Automatically renaming worksheets to weekdays

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.

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)

<[email protected]> wrote in message
news:[email protected]...
> 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.
>

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

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)

<[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
> > 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
>

Bob Phillips wrote:
> 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.