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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

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
 

Forum statistics

Threads
1,141,914
Messages
5,709,313
Members
421,627
Latest member
bennhrios9

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