VBA coding: macro to create a new sheet and have it named by data in a cell and the date

The code I have right now will create a new sheet and insert the date, as the name of the sheet, but I'd like the code to do that and use data from a cell, as the name of the worksheet.

here's the current code:

Sub CheckAndAddSheetNameAsCurrentDate()

Dim SheetName As String

SheetName = Format(Date, "mm-dd-yy")

On Error GoTo AddNew
Sheets(SheetName).Activate
Exit Sub

AddNew:
Sheets.Add , Worksheets(Worksheets.Count)
ActiveSheet.Name = SheetName

End Sub


For bonus points, I'm trying to find out how to add a static date using a macro, that when a new worksheet template is opened it inserts the current date into this cell, and then NEVER changes it. I'm also looking to share a workbook, but lock a worksheet of that workbook. And I need to then create a worksheet of this workbook where all of this data is updated, but on a day by day basis and its clearly stated what day the data is from. (I have more requests, but I'm working this through.)

This would use the value in A1 as part of the new sheet's name.

Please Login or Register  to view this content.

Hope that helps.

Thanks John! That worked perfectly!

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

John, is it possible to insert another VBA code into this one and have both still work?

This is the code I'm looking to add:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

And I would add that code and this one into one code...

Sub CheckAndAddSheetNameAsCurrentDate()

Dim SheetName As String

SheetName = Range("C1").Value & " " & Format(Date, "mm-dd-yy")

On Error GoTo AddNew
Sheets(SheetName).Activate
Exit Sub

AddNew:
Sheets.Add , Worksheets(Worksheets.Count)
ActiveSheet.Name = SheetName

End Sub

You could probably call it?

Please Login or Register  to view this content.

Ok, I don't know why I'm trying to combine things I don't understand...lol...what I'm trying to do is make a date field that is static and is linked to the creation of a new cell. So, when I enter something into cell A4, that would make the cell I1 have the current date entered into it (in mm/dd/yy format)...