Naming a new sheet using the NOW formula

JJbug

Board Regular
Joined
Mar 16, 2012
Messages
68
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.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,515
Office Version
  1. 2019
Platform
  1. Windows
Hi,
the Now() function in way you are using it will contain illegal tab name characters which is probably why you are getting the error. You get around this by formatting the result from Now() to you variable into an acceptable tab name.

so your code could look something like this:

Code:
Sub ChangeLog()
    Dim sheet_name_to_create As String
    With Sheet1
        .Range("B4").Value = "Change Submission"
        .Range("C4").Value = Now()
        sheet_name_to_create = Format(.Range("C4"), "dd-mm-yyyy hh-mm-ss")
    End With
    Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = _
    sheet_name_to_create
End Sub

You will note that I have removed the part of the code that tests for an existing sheet name - as using Now() & formatting to include HH-MM-SS. It is unlikely that each time you run the code you would have the same TAB name value in your variable.

Hope Helpful

Dave
 

JJbug

Board Regular
Joined
Mar 16, 2012
Messages
68
Hi,
the Now() function in way you are using it will contain illegal tab name characters which is probably why you are getting the error. You get around this by formatting the result from Now() to you variable into an acceptable tab name.

so your code could look something like this:

Code:
Sub ChangeLog()
    Dim sheet_name_to_create As String
    With Sheet1
        .Range("B4").Value = "Change Submission"
        .Range("C4").Value = Now()
        sheet_name_to_create = Format(.Range("C4"), "dd-mm-yyyy hh-mm-ss")
    End With
    Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = _
    sheet_name_to_create
End Sub

You will note that I have removed the part of the code that tests for an existing sheet name - as using Now() & formatting to include HH-MM-SS. It is unlikely that each time you run the code you would have the same TAB name value in your variable.

Hope Helpful

Dave

Very helpful indeed. Thank you.

I was using a relative reference so each change submission was listing on a master tab. After using your code the relative movement of text is no longer working, the active cell is moving down row by row, unfortunately the data is not being added to the cells.

Any ideas?
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,515
Office Version
  1. 2019
Platform
  1. Windows
try this:

Code:
Sub ChangeLog()
    Dim sheet_name_to_create As String
    With Sheet1
    lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
        .Cells(lastrow, 2).Value = "Change Submission"
        .Cells(lastrow, 3).Value = Now()
        sheet_name_to_create = Format(.Cells(lastrow, 3), "dd-mm-yyyy hh-mm-ss")
    End With
    Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = _
    sheet_name_to_create
End Sub

Dave
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

The reason for the original failure is exactly as Dave said.
The Now function is Date+Time
and the Date contains / which is an illegal character in a sheetname.

The only thing you need to change in your original code is

sheet_name_to_create = Sheet1.Range("C4").Value
to
sheet_name_to_create = Format(Sheet1.Range("C4").Value,"mm-dd-yyyy")
 

JJbug

Board Regular
Joined
Mar 16, 2012
Messages
68
The reason for the original failure is exactly as Dave said.
The Now function is Date+Time
and the Date contains / which is an illegal character in a sheetname.

The only thing you need to change in your original code is

sheet_name_to_create = Sheet1.Range("C4").Value
to
sheet_name_to_create = Format(Sheet1.Range("C4").Value,"mm-dd-yyyy")

Jonmo1

I am getting an error with the original code

Run-time error '1004'
Applicatio-defined or object-defined error


This is refering to the last line
Sheets(ActiveSheet.Name).Name = sheet_name_to_create

I am not sure what I the issue is, I believe it has to do with Format(Sheet1.Range("C4").Value,"mm-dd-yyyy") I just dont know well enough to pinpoint this issue.


JJBug
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

What exactly is in Sheet1.Range("C4").Value ?

Try changing
sheet_name_to_create = Sheet1.Range("C4").Value
to
sheet_name_to_create = Format(Date,"mm-dd-yyyy")
 

JJbug

Board Regular
Joined
Mar 16, 2012
Messages
68
What exactly is in Sheet1.Range("C4").Value ?

Try changing
sheet_name_to_create = Sheet1.Range("C4").Value
to
sheet_name_to_create = Format(Date,"mm-dd-yyyy")

Jonmo,

That almost works, now it gives me the error for same name. I tried to format the hh-mm into the code and it works once a minute but results in the following name Jan-7-2014-00-00.

Any additional ideas
 

JJbug

Board Regular
Joined
Mar 16, 2012
Messages
68
Something else I just realized is (NOW) is not the best method to add a date into a log as this will continue to change and will not keep a good record of the date and time changed.

It appears I still have a lot to learn.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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")
 

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,265
Members
416,963
Latest member
samfuge

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top