worksheet.name = date + time

[nada]

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

 

[DominicB]

Good morning nada

How about something like this :

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]

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]

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]

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