Naming a new sheet using the NOW formula

[JJbug]

Hello,

Your local Novice here. I am trying to create a log for changes made to a master data workbook. So far I am having some issue with naming the new sheet created by the macro, see code below.

Sub ChangeLog()
ActiveCell.Select
ActiveCell.FormulaR1C1 = "Change Submission"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=NOW()"
ActiveCell.Offset(1, -1).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveCell.Offset(1, 0).Range("A1").Select

sheet_name_to_create = Sheet1.Range("C4").Value
For rep = 1 To (Worksheets.Count)
If LCase(Sheets(rep).Name) = LCase(sheet_name_to_create) Then
MsgBox "This sheet already exists!"
Exit Sub
End If


Next



Sheets.Add After:=Sheets(Sheets.Count)
Sheets(ActiveSheet.Name).Name = sheet_name_to_create

End Sub


_______________________________________


This is the begining of the code, next it will open master file and move a copy of all the changed data to the new sheet which should be named as the date of the change submission.

Please let me know if you need clarification on any details.

Thank you again for all of your help you all are the best.

 

[JJbug]

Yes you are right, I wanted to use the Date and Time as the sheet name which is working fine or well close.
Additionally I am logging each date and time the action is run. This is being logged on a tab called "Change Log", the following is an example of what the Change Log will look like. As you can see the NOW function will not work unless I use a paste special after each entry. Also I will try to add a Version # to each entry.

This is only a small piece of the larger puzzle I am trying to assemble. In the end this macro will be added to a set of master files to log and save any changes made for easier review and audit.

BUTTON

Change Submission</SPAN>	1/8/2014 8:10</SPAN>
Change Submission</SPAN>	1/8/2014 8:10</SPAN>
Change Submission</SPAN>	1/8/2014 8:10</SPAN>
Change Submission</SPAN>	1/8/2014 8:10</SPAN>
Change Submission</SPAN>	1/8/2014 8:10</SPAN>
Change Submission</SPAN>	1/8/2014 8:10</SPAN>
Change Submission</SPAN>	1/8/2014 8:10</SPAN>
Change Submission</SPAN>
Change Submission</SPAN>	#NAME?</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

 

[JJbug]

I thought you only wanted the DATE in the sheet name, so I used the Date function.

Try changing that to NOW
sheet_name_to_create = Format(Now, "mmm-d-yyyy-hh-mm")

Yes you are right, I wanted to use the Date and Time as the sheet name which is working fine or well close.
Additionally I am logging each date and time the action is run. This is being logged on a tab called "Change Log", the following is an example of what the Change Log will look like. As you can see the NOW function will not work unless I use a paste special after each entry. Also I will try to add a Version # to each entry.

This is only a small piece of the larger puzzle I am trying to assemble. In the end this macro will be added to a set of master files to log and save any changes made for easier review and audit.

BUTTON

Change Submission</SPAN>	1/8/2014 8:10</SPAN>
Change Submission</SPAN>	1/8/2014 8:10</SPAN>
Change Submission</SPAN>	1/8/2014 8:10</SPAN>
Change Submission</SPAN>	1/8/2014 8:10</SPAN>
Change Submission</SPAN>	1/8/2014 8:10</SPAN>
Change Submission</SPAN>	1/8/2014 8:10</SPAN>
Change Submission</SPAN>	1/8/2014 8:10</SPAN>
Change Submission</SPAN>
Change Submission</SPAN>	#NAME?</SPAN>

<TBODY>
</TBODY>

 

[Jonmo1]

Try

Sub Change_Log()
Dim NewName As String, ws As Worksheet
NewName = Format(Now, "mmm-d-yyyy hh.mm")
For Each ws In Worksheets
    If ws.Name = NewName Then
        MsgBox "Sheet Already Exists"
        Exit Sub
    End If
Next ws
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = NewName
With Sheets("Change Log").Range("A" & Rows.Count).End(xlUp)
    .Offset(1, 0).Value = "Change Submission"
    .Offset(1, 1).Value = Now
    .Offset(1, 1).NumberFormat = "m/d/yyyy h:mm"
End With
End Sub