Copy sheet, values only, naming with today's date

[CatieBear93]

I am trying to copy an entire sheet, pasting only the values and renaming the new sheet to the current date.
This code allows me to input the date manually, but it copies formatting and the formulas too.

Sub AddNameNewSheet1()
Dim Newname As String
Newname = InputBox("Name for new worksheet?")
If Newname <> "" Then
Sheets.Add Type:=xlWorksheet
ActiveSheet.Name = Newname
End If
End Sub

 

[CatieBear93]

UPDATE:
I was able to get the code to automatically name it the current date, however it is still copying over the formatting

Sub CopySheet_TodayDate()
'Updateby20140717
    Dim newName As String
    Dim szTodayDate As String
    szTodayDate = Format(Date, "mmm-dd-yyyy")
    
    On Error Resume Next
    newName = szTodayDate

    If newName <> "" Then
        ActiveSheet.Copy After:=Worksheets(Sheets.Count)
        On Error Resume Next
        ActiveSheet.Name = newName
    End If
End Sub

 

[GWteB]

Hi & welcome to MrExcel.

How about ...

Sub CatieBear93()
    With ActiveSheet
        .Cells.Copy
        With .Parent.Sheets.Add
            .Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            .Range("A1").Select
            .Name = VBA.Format$(VBA.Date$, "mmm-dd-yyyy")
        End With
    End With
    Application.CutCopyMode = False
End Sub

 

[CatieBear93]

Hi & welcome to MrExcel.

How about ...

Sub CatieBear93()
    With ActiveSheet
        .Cells.Copy
        With .Parent.Sheets.Add
            .Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            .Range("A1").Select
            .Name = VBA.Format$(VBA.Date$, "mmm-dd-yyyy")
        End With
    End With
    Application.CutCopyMode = False
End Sub

This worked! Thank you for your help.

 

[GWteB]

You are welcome and thanks for the feedback