Macro to change tab name to date from a cell ref

[Guest]

Hey all

Here we go. The workbook contains sheets for each business day of the month. Each day a new sheet is added. The user renames the added sheet to "New" and clicks an Update button to compile the data onto a summary worksheet. I would like the macro to also rename the added sheet to the date that appear in cell E2 in this format: yyyymmdd. I have tried to format the date in the cell, but the macro gives me the error CANNOT USE [ or / . Although the date appears as yyyymmdd, in the formula bar it is still mm/dd/yyyy

Any help is greatly appreciated.

 

[Frank Kabel]

Hi
try something like
Dim wks_new as worksheet
....
set wks_new = worksheets("new name")
wks_new.name = Format(wks_new.range("E2").value, "yyyymmdd")

--
Regards
Frank Kabel
Frankfurt, Germany

Hey all,

Here we go. The workbook contains sheets for each business day of

the month. Each day a new sheet is added. The user renames the added
sheet to "New" and clicks an Update button to compile the data onto a
summary worksheet. I would like the macro to also rename the added
sheet to the date that appear in cell E2 in this format: yyyymmdd. I
have tried to format the date in the cell, but the macro gives me the
error CANNOT USE [ or / . Although the date appears as yyyymmdd, in
the formula bar it is still mm/dd/yyyy.

 

[JE McGimpsey]

One way:

With Worksheets("NEW")
.Name = Format(.Range("E2").Value, "yyyymmdd")
End With

 

[Tom Ogilvy]

With Worksheets("New")
.Name = format(.Range("E2").Value,"yyyymmdd")
End With

--
Regards,
Tom Ogilvy

Hey all,

Here we go. The workbook contains sheets for each business day of the

month. Each day a new sheet is added. The user renames the added sheet to
"New" and clicks an Update button to compile the data onto a summary
worksheet. I would like the macro to also rename the added sheet to the
date that appear in cell E2 in this format: yyyymmdd. I have tried to
format the date in the cell, but the macro gives me the error CANNOT USE [
or / . Although the date appears as yyyymmdd, in the formula bar it is
still mm/dd/yyyy.

 

[Ron de Bruin]

Try this

With Worksheets("New")
..Name = Format(.Range("E2"), "yyyymmdd")
End With

Or

With Worksheets("New")
..Name = Format(.Range("E2"), "yyyy-mm-dd")
End With




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)

Hey all,

Here we go. The workbook contains sheets for each business day of the month. Each day a new sheet is added. The user

renames the added sheet to "New" and clicks an Update button to compile the data onto a summary worksheet. I would like the
macro to also rename the added sheet to the date that appear in cell E2 in this format: yyyymmdd. I have tried to format the
date in the cell, but the macro gives me the error CANNOT USE [ or / . Although the date appears as yyyymmdd, in the formula
bar it is still mm/dd/yyyy.