Add a sheet with dynamic Sheet name in Excel as DATE

Question

I use this code to create a new sheet with a dynamic name based on the real-time but it is not successful run, show

error 1004 "application-defined or object-defined error".

Private Sub Compute_Click()

    With ThisWorkbook
    Worksheets("Data").Range("A1").Value = Now
    If IsDate(Worksheets("Data").Range("A1")) Then
        Set Worksheet = .Sheets.Add(After:=.Sheets(.Sheets.Count))

       Worksheet.Name = Format(Range("A1"), "MM-DD-YYYY hh-mm-ss")

    End If
    End With
End Sub

BigBen BigBen · Accepted Answer · 2019-08-03T02:42:01

A couple of points:

Use something other than Worksheet as your variable.
You haven't specified which worksheet Range("A1") is on. That means there's an implicit ActiveSheet, which may not be (is not) the Data sheet.
Now is a date so the IsDate check is unnecessary.

All that said, your revised code might look something like this:

Private Sub Compute_Click()
    With ThisWorkbook
        Dim ws As Worksheet
        Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))

        ws.Name = Format(Now, "MM-DD-YYYY hh-mm-ss")
    End With
End Sub

Add a sheet with dynamic Sheet name in Excel as DATE

1 Answers