Insert date automatically into cell

[Mötley]

Hello,

is it possible that when I add new sheet into workbook, macro adds specific date into cell?

I'm using this code to add date, its attached to commandbutton

Sub SetDate()
ActiveSheet.Unprotect
'Set cell D2 to the current date
ActiveSheet.Range("D2").Value = Date
ActiveSheet.Protect
End Sub

Also. this is the code I'm using to add new sheet

Sub UusiSivu()
ActiveWorkbook.Unprotect
Dim CurrentDay As Integer
Dim NewName As String
Dim WS As Worksheet
Set WS = ActiveSheet
If IsNumeric(Right(WS.Name, 2)) Then
   CurrentDay = Right(WS.Name, 2)
ElseIf IsNumeric(Right(WS.Name, 1)) Then
   CurrentDay = Right(WS.Name, 1)
Else
   Exit Sub
End If
CurrentDay = CurrentDay + 1
NewName = Format(Date, "dd.mm.yyyy")
Dim checkWs As Worksheet
On Error Resume Next
Set checkWs = Worksheets(NewName)
If checkWs Is Nothing Then
'Copies the current sheet to the end of the workbook
    Sheets("Default").Copy after:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = NewName
  Dim oleObj As OLEObject
Else
   Set checkWs = Nothing
   MsgBox "Uusi taulukko voidaan lisätä huomenna."
End If
ActiveWorkbook.Protect
End Sub

Let's say I add new sheet, its named after specific date, but date also needs to appear into cell, maybe "D7", how do I automate it?

 

[Trevor G]

You can add a date to a cell and if you want this to occur when you add a new sheet and also change the sheet name then something like this would work.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> nameNewSheet()<br>Sheets.Add<br>ActiveSheet.Name = Format(Date, "dd mmm yyyy")<br>Range("D7") = ActiveSheet.Name<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Mötley]

Thanks! Got it!