name worksheet  
Author Message
AndersBank





PostPosted: Visual Basic for Applications (VBA), name worksheet Top

I want to name a worksheet in excel using a macro. 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



Microsoft ISV Community Center Forums1  
 
 
Jon Peltier





PostPosted: Visual Basic for Applications (VBA), name worksheet Top

Use Date & " " & Time. Ampersands are for concatenation in VBA. You can sometimes get away with "+", but it's better to do it correctly all the time, rather than get used to a way that sometimes works and somwtimes gives unexpected results.

Also, sheet names are limited to 31 characters, and some characters are disallowed in worksheet names, including: \ / * [ ]

It's also a good idea to avoid allowed characters that will cause confusion, such as ' " ! + - &

I stick with letters, numbers, spaces, and the underscore character.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com