Add a new sheet and rename tab with a date

Hi

I have a timesheet spreadsheet which I use to record the work I'm doing. Every week I create a new sheet. I have created a macro that automatically copies the sheet "Wk End 4 Jul" and clears the content, but I would like it to name the tab using the following format "Wk End d mmm" where the d mmm represents the Friday of the week which I'm creating it for. Can anybody advise how I adapt my exisiting macro to add this functionality as you will see I intitiate the macro with Ctrl+Shift+N:

Sub NewSheet()
' NewSheet Macro
' Creates a new sheet
' Keyboard Shortcut: Ctrl+Shift+N
Sheets("Wk End 4 Jul").Select
Sheets("Wk End 4 Jul").Copy After:=Sheets(13)
Sheets("Wk End 4 Jul (2)").Select
Sheets("Wk End 4 Jul (2)").Name = "Wk End d mmm"
Range("B4:D92").Select
Selection.ClearContents
Range("G4:H57").Select
Selection.ClearContents
Range("L4:L92").Select
Selection.ClearContents
Range("E91:F91").Select
Selection.AutoFill Destination:=Range("E4:F91"), Type:=xlFillDefault
Range("C4").Select

End Sub

I suspect there is an issue with the above code in that it puts the new sheet "After" the 4 Jul sheet which is fine for this weeks tab (11 Jul) but will mean that the 18 Jul tab will be out of order.

Thanks in advance.

Chris

Brilliant, it works perfectly. Is it possible to to tweek the code so that it puts new sheet at the end an not next to the sheet the copy is based upon?

Maybe:

Sub chrisrick()
Dim x As String
x = Day(Date - 3) & " " & UCase(Format(DateAdd("m", 0, Date), "MMM"))
Sheets("Wk End " & x).Select
Sheets("Wk End " & x).Copy After:=Sheets(Sheets.count)
Sheets("Wk End 4 Jul (2)").Select
ActiveSheet.Name = "Wk End " & Day(Date + 4) & " " & UCase(Format(DateAdd("m", 0, Date), "MMM"))
Range("B4:D92").Select
Selection.ClearContents
Range("G4:H57").Select
Selection.ClearContents
Range("L4:L92").Select
Selection.ClearContents
Range("E91:F91").Select
Selection.AutoFill Destination:=Range("E4:F91"), Type:=xlFillDefault
Range("C4").Select
End Sub

Thanks John, that works really well. Your help is appreciated.

You're welcome. Glad to help out and thanks for the feedback.

BTW Chris, If your problem is solved, please mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes.

Chris:

I noticed a potential problem so I made a modification:

Sub chrisrick()
Dim x As String
x = Day(Date - 3) & " " & UCase(Format(DateAdd("m", 0, Date), "MMM"))
Sheets("Wk End " & x).Select
Sheets("Wk End " & x).Copy After:=Sheets(Sheets.count)
ActiveSheet.Name = "Wk End " & Day(Date + 4) & " " & UCase(Format(DateAdd("m", 0, Date), "MMM"))
Range("B4:D92").Select
Selection.ClearContents
Range("G4:H57").Select
Selection.ClearContents
Range("L4:L92").Select
Selection.ClearContents
Range("E91:F91").Select
Selection.AutoFill Destination:=Range("E4:F91"), Type:=xlFillDefault
Range("C4").Select
End Sub