worksheet.name = date + time

nada

Board Regular
Joined
Jul 4, 2006
Messages
193
I want to name a worksheet. I want the name to be the current date. If there already exists a worksheet with that name I want to add time to the name. The problem is that I do not know how to add time to the name of the worksheet. pls help me! any help appreciated!

i = Worksheets.Count
Worksheets.Add Count:=1, After:=Sheets(i)

For j = 1 To i
If Sheets(j).Name = Date Then
blnTodaySheet = True
End If
Next

If blnTodaySheet = True Then
Sheets(i + 1).Name = Date + Time
Else
Sheets(i + 1).Name = Date & ":" & Time
End If
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Good morning nada

How about something like this :

Code:
Sub test()
a = Application.WorksheetFunction.Substitute(Date, "/", "-")
i = Worksheets.Count
Worksheets.Add Count:=1, After:=Sheets(i)
For j = 1 To i
If Sheets(j).Name = a Then
a = Application.WorksheetFunction.Substitute(Format(Now, "dd-mm-yyyy hh-mm"), "/", "-")
End If
Next
Sheets(i + 1).Name = a
End Sub

I have used the substitute command to use "-" as the seperator rather than "/" (which we use in England - not sure about you), which of course is not allowed in sheet names.

HTH

DominicB
 

Bobscooks

New Member
Joined
Jan 5, 2006
Messages
12
I use this method:

(in a VB macro)

Range("b1").Select
savepath = ActiveCell.Value
Range("c1").Select
savexl = ActiveCell.Value & ".xls"
ActiveWorkbook.SaveAs Filename:=savepath & savexl, FileFormat:=xlNormal
ActiveWorkbook.Save

Where c1 is the following "="S06 Balearics Accomm "&IF(DAY(F1)<10,"0","")&DAY(F1)&"-"&IF(MONTH(F1)<10,"0","")&MONTH(F1)&"-"&RIGHT(YEAR(F1),2)"

and F1 (from the above code) is =today()
 

nada

Board Regular
Joined
Jul 4, 2006
Messages
193
hi! thanks for your help. however i still have a small problem. i want to find the worksheet(s) that have the current date or the current date plus the time. Now I search simply for the ones with the current date (only):

pls help me with the code for searching for the optional time also. the name then looks like eg 2006-09-15 14.15 (ie YYYY-MM-DD HH.MM). thanks alot!
For j = 1 To i + 1
If Sheets(j).Name = Format(Date, "YYYY-MM-DD") Then 'wrong
blnIsTodaySheet = True
k = k + 1
End If
Next
 

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Hi nada

Why? What is it you're trying to do?

The code above decides on the sheet name and puts it into a variable (a), so this should always hold the name of the sheet you've just created, whether it is just date or date plus time. Does that help you?

HTH

DominicB
 

Watch MrExcel Video

Forum statistics

Threads
1,114,051
Messages
5,545,727
Members
410,702
Latest member
clizama18
Top