Date as Sheet Name

[jlclyde]

I am trying to take a date from each sheet and name the tab. Each
date is in Range("F10") Here is the code I have so far. I can get it
to transfer names and other things, but it reads dates with the /
character, which excel does not liek to have in the name of a sheet.

Sub ListSheets()
Application.Calculation = xlCalculationManual

Dim ws As Worksheet
Dim Str As String
Dim x As Integer

x = 1
For Each ws In Worksheets

Str = Range("F10")
ws.Name = Str
x = x + 1
Next ws
Application.Calculation = xlCalculationAutomatic
End Sub

Thanks,
Jay

 

[jlclyde]

I am trying to take a date from each sheet and name the tab.  Each
date is in Range("F10")  Here is the code I have so far.  I can get it
to transfer names and other things, but it reads dates with the /
character, which excel does not liek to have in the name of a sheet.

Sub ListSheets()
Application.Calculation = xlCalculationManual

Dim ws As Worksheet
Dim Str As String
Dim x As Integer

x = 1
For Each ws In Worksheets

    Str = Range("F10")
    ws.Name = Str
     x = x + 1
Next ws
Application.Calculation = xlCalculationAutomatic
End Sub

Thanks,
Jay

Sub ListSheets()
Application.Calculation = xlCalculationManual

Dim ws As Worksheet
Dim Str As String
Dim x As Integer

x = 1
For Each ws In Worksheets

Str = Application.WorksheetFunction.Text(ws.Range("F10"), "mmm dd
yy")
ws.Name = Str
x = x + 1
Next ws
Application.Calculation = xlCalculationAutomatic
End Sub

I figured it out. Change the dates to text formatted how you would
liek to see them on the sheet tabs and then put them in.

Thanks,
Jay

 

[Bernie Deitrick]

Jay,

Use this for Str:

Str = Format(ws.Range("F10").Value, "mmm dd yyyy")


Replace the mmm dd yyyy with any valid date format that you prefer, and leave out the /s.

HTH,
Bernie
MS Excel MVP