Copy Paste Worksheet with new Sheet name (1 Viewer)

[ECEK]

I have the following code in an assigned macro to copy and paste to a new sheet.

What I would like to do is to be able to save the sheet as a specific name (in this case the date as six figurs eg. 030615) based on a cell.

Does anybody know how I would edit my code to do this ?

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+m
'
Cells.Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Cells.Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Sheets("Weekly Team Performance").Select
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("State of Play").Select
Range("C3").Select
End Sub

 

[smiler44]

Try putting the code after the line that says ActiveSheet.Paste
I think the sample code below will change the name of the existing work sheet.
Sheet2 being the existing name of the sheet you want to rename, GO being the new name

 Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Go"

smiler44

 

[ECEK]

I added:


ActiveSheet.Name = Worksheets("State of Play").Range("B4").Value
after
ActiveSheet.Paste