Date as Sheet Name
 

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

Date as Sheet Name
 

On Feb 3, 10:06*am, jlclyde wrote:
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

Date as Sheet Name
 

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


"jlclyde" wrote in message
...
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