Naming a Sheet with a function

  • Thread starter Thread starter MónicaM
  • Start date Start date
M

MónicaM

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
 
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)
 
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
 
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
 
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 said:
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
Click to expand...
 
Back
Top