Naming a Sheet with a function
 

I want to put in the sheet name tab the actual date. I have tried the
following macro but it does not work:
Sub nombrar()

Dim fecha As String

fecha = Date
ActiveCell.Value = fecha ‘this is only for testing that fecha is
a string with the date
ActiveSheet.Name = fecha

End Sub

I get an error in the last line. If I put fecha between quotes I get
the name fecha of course, insted of his value


--
MónicaM
------------------------------------------------------------------------
MónicaM's Profile: http://www.excelforum.com/member.php...o&userid=22523
View this thread: http://www.excelforum.com/showthread...hreadid=374562

Naming a Sheet with a function
 

You probably have illegal characters in your date format (e.g., "/")

Try

ActiveSheet.Name = Format(Date, "dd-mmm-yyyy")

substitute your desired date format (with legal characters)



In article ,
MónicaM wrote:

I want to put in the sheet name tab the actual date. I have tried the
following macro but it does not work:
Sub nombrar()

Dim fecha As String

fecha = Date
ActiveCell.Value = fecha ‘this is only for testing that fecha is
a string with the date
ActiveSheet.Name = fecha

End Sub

I get an error in the last line. If I put fecha between quotes I get
the name fecha of course, insted of his value

Naming a Sheet with a function
 

Monica

You can't use / in a sheet name which is the problem. Try using the Format
function to format the date so that only legal characters are included.

Sub test()

Dim fecha As Date

fecha = Date

ActiveSheet.Name = Format(fecha, "yyyy_mm_dd")

End Sub

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

MónicaM wrote:
I want to put in the sheet name tab the actual date. I have tried the
following macro but it does not work:
Sub nombrar()

Dim fecha As String

fecha = Date
ActiveCell.Value = fecha ‘this is only for testing that fecha is
a string with the date
ActiveSheet.Name = fecha

End Sub

I get an error in the last line. If I put fecha between quotes I get
the name fecha of course, insted of his value

Naming a Sheet with a function
 

Hi Monica,

Your sheet naming routing works fine for me. It would fail of course if
another sheet is named with today's date. If you need multiple sheets
similarly named with Date try this:

Sub test2()
Dim fecha As String
Dim sht As Object
n = Date
fecha = CStr(Date)
For Each sht In ActiveWorkbook.Sheets
If Left(sht.Name, Len(fecha)) = fecha Then
cnt = cnt + 1
End If
Next
If cnt Then fecha = fecha & Chr(65 + cnt)

ActiveSheet.Name = fecha
End Sub

Regards,
Peter T

"MónicaM" wrote in
message ...

I want to put in the sheet name tab the actual date. I have tried the
following macro but it does not work:
Sub nombrar()

Dim fecha As String

fecha = Date
ActiveCell.Value = fecha ‘this is only for testing that fecha is
a string with the date
ActiveSheet.Name = fecha

End Sub

I get an error in the last line. If I put fecha between quotes I get
the name fecha of course, insted of his value


--
MónicaM
------------------------------------------------------------------------
MónicaM's Profile:
http://www.excelforum.com/member.php...o&userid=22523
View this thread: http://www.excelforum.com/showthread...hreadid=374562

Naming a Sheet with a function
 

Reading JE's & Dick's comments I realise your routine only worked for me
because my default/system date format is "dd-mm-yyyy", namely does not
include a "/".

Peter T

"Peter T" <peter_t@discussions wrote in message
...
Hi Monica,

Your sheet naming routing works fine for me. It would fail of course if
another sheet is named with today's date. If you need multiple sheets
similarly named with Date try this:

Sub test2()
Dim fecha As String
Dim sht As Object
n = Date
fecha = CStr(Date)
For Each sht In ActiveWorkbook.Sheets
If Left(sht.Name, Len(fecha)) = fecha Then
cnt = cnt + 1
End If
Next
If cnt Then fecha = fecha & Chr(65 + cnt)

ActiveSheet.Name = fecha
End Sub

Regards,
Peter T

"MónicaM" wrote in
message ...

I want to put in the sheet name tab the actual date. I have tried the
following macro but it does not work:
Sub nombrar()

Dim fecha As String

fecha = Date
ActiveCell.Value = fecha ‘this is only for testing that fecha is
a string with the date
ActiveSheet.Name = fecha

End Sub

I get an error in the last line. If I put fecha between quotes I get
the name fecha of course, insted of his value


--
MónicaM
------------------------------------------------------------------------
MónicaM's Profile:
http://www.excelforum.com/member.php...o&userid=22523
View this thread:
http://www.excelforum.com/showthread...hreadid=374562