I am using this macro for naming my tabs with the content of cell A1 on each
worksheet, my problem is that it wont work with a date, and I need to have a
date in cell A! to name each tab with that date. Any ideas would help.

It'll work with a date--just not if you format that date so that it wouldn't
make a valid worksheet name--slashes are not allowed.

dim wks as worksheet
for each wks in thisworkbook.worksheets
wks.name = format(wks.range("a1").value, "yyyy-mm-dd")
next wks



JackR wrote:

I am using this macro for naming my tabs with the content of cell A1 on each
worksheet, my problem is that it wont work with a date, and I need to have a
date in cell A! to name each tab with that date. Any ideas would help.

--

Dave Peterson

I realized I did not include the macreo I am runing, so here it is, how do I
incorporate this to label the tabs,JUL-1, JUL-2 etc based on what I enter in
cell 1 of each worksheet.

Here is what I am using now.
Sub wsname()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Name = ws.Cells(1, 1).Value
Next ws
End Sub


"Dave Peterson" wrote:

It'll work with a date--just not if you format that date so that it wouldn't
make a valid worksheet name--slashes are not allowed.

dim wks as worksheet
for each wks in thisworkbook.worksheets
wks.name = format(wks.range("a1").value, "yyyy-mm-dd")
next wks



JackR wrote:

I am using this macro for naming my tabs with the content of cell A1 on each
worksheet, my problem is that it wont work with a date, and I need to have a
date in cell A! to name each tab with that date. Any ideas would help.

--

Dave Peterson

Since "won't work" is none too descriptive, I will assume that your macro throws
an error.

Ususally due to the illegal / marks in a date.

Try this macro.

Sub wsname()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Name = Format((ws.Cells(1, 1).Value), "mmm dd yyyy")
Next ws
End Sub


Gord Dibben MS Excel MVP

On Sun, 24 Jun 2007 14:58:02 -0700, JackR
wrote:

I am using this macro for naming my tabs with the content of cell A1 on each
worksheet, my problem is that it wont work with a date, and I need to have a
date in cell A! to name each tab with that date. Any ideas would help.

ws.Name = format(ws.Cells(1, 1).Value, "mmm-d")



JackR wrote:

I realized I did not include the macreo I am runing, so here it is, how do I
incorporate this to label the tabs,JUL-1, JUL-2 etc based on what I enter in
cell 1 of each worksheet.

Here is what I am using now.
Sub wsname()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Name = ws.Cells(1, 1).Value
Next ws
End Sub

"Dave Peterson" wrote:

It'll work with a date--just not if you format that date so that it wouldn't
make a valid worksheet name--slashes are not allowed.

dim wks as worksheet
for each wks in thisworkbook.worksheets
wks.name = format(wks.range("a1").value, "yyyy-mm-dd")
next wks



JackR wrote:

I am using this macro for naming my tabs with the content of cell A1 on each
worksheet, my problem is that it wont work with a date, and I need to have a
date in cell A! to name each tab with that date. Any ideas would help.

--

Dave Peterson


--

Dave Peterson

If you already formatted A1 to show what you want, you could use this, too:

ws.Name = ws.Cells(1, 1).Text



JackR wrote:

I realized I did not include the macreo I am runing, so here it is, how do I
incorporate this to label the tabs,JUL-1, JUL-2 etc based on what I enter in
cell 1 of each worksheet.

Here is what I am using now.
Sub wsname()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Name = ws.Cells(1, 1).Value
Next ws
End Sub

"Dave Peterson" wrote:

It'll work with a date--just not if you format that date so that it wouldn't
make a valid worksheet name--slashes are not allowed.

dim wks as worksheet
for each wks in thisworkbook.worksheets
wks.name = format(wks.range("a1").value, "yyyy-mm-dd")
next wks



JackR wrote:

I am using this macro for naming my tabs with the content of cell A1 on each
worksheet, my problem is that it wont work with a date, and I need to have a
date in cell A! to name each tab with that date. Any ideas would help.

--

Dave Peterson


--

Dave Peterson

Sorry, I realize wont work is not descriptive.

I changed the macro to read as follows:

Sub namesheetfromcell()
ActiveSheet.Name = Format(Range("a1"), "mmm-dd")
End Sub

THis way it would only change one worksheet at a time, what I was trying to
accomplish is this;

I have 32 tabs in my workbook, Sheet 1 (tab 1) is where I would like to
input a date say July 1, I then want a macro to run that will fill each tab
name starting with the tab 2 to tab 31 with july, 1, july 2, etc.

hopefully this makes sense and you could help me figure this out as I am
stumped right now.

Thanks
"Gord Dibben" wrote:

Since "won't work" is none too descriptive, I will assume that your macro throws
an error.

Ususally due to the illegal / marks in a date.

Try this macro.

Sub wsname()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Name = Format((ws.Cells(1, 1).Value), "mmm dd yyyy")
Next ws
End Sub


Gord Dibben MS Excel MVP

On Sun, 24 Jun 2007 14:58:02 -0700, JackR
wrote:

I am using this macro for naming my tabs with the content of cell A1 on each
worksheet, my problem is that it wont work with a date, and I need to have a
date in cell A! to name each tab with that date. Any ideas would help.

Jack

You have changed the rules on us.

We thought you wanted a pre-entered date from each sheet's A1.

Try this from Chip Pearson. Doesn't need a date in A1 and assumes you have 31
sheets, not 32.

Sub NameSheets()
'Chip Pearson Feb 14th, 2007
Dim Ndx As Long
Dim StartMonth As Variant
StartMonth = Application.InputBox(Prompt:="Enter the month number.", Type:=1)
If StartMonth = False Then
Exit Sub
End If
For Ndx = 1 To ActiveWorkbook.Worksheets.Count
ActiveWorkbook.Worksheets(Ndx).Name = Format(DateSerial( _
IIf(StartMonth = 1, Year(Now) + 1, Year(Now)), StartMonth, Ndx), _
"dd mmm yyyy")
Next Ndx
End Sub


Gord

On Sun, 24 Jun 2007 16:15:01 -0700, JackR
wrote:

Sorry, I realize wont work is not descriptive.

I changed the macro to read as follows:

Sub namesheetfromcell()
ActiveSheet.Name = Format(Range("a1"), "mmm-dd")
End Sub

THis way it would only change one worksheet at a time, what I was trying to
accomplish is this;

I have 32 tabs in my workbook, Sheet 1 (tab 1) is where I would like to
input a date say July 1, I then want a macro to run that will fill each tab
name starting with the tab 2 to tab 31 with july, 1, july 2, etc.

hopefully this makes sense and you could help me figure this out as I am
stumped right now.

Thanks
"Gord Dibben" wrote:

Since "won't work" is none too descriptive, I will assume that your macro throws
an error.

Ususally due to the illegal / marks in a date.

Try this macro.

Sub wsname()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Name = Format((ws.Cells(1, 1).Value), "mmm dd yyyy")
Next ws
End Sub


Gord Dibben MS Excel MVP

On Sun, 24 Jun 2007 14:58:02 -0700, JackR
wrote:

I am using this macro for naming my tabs with the content of cell A1 on each
worksheet, my problem is that it wont work with a date, and I need to have a
date in cell A! to name each tab with that date. Any ideas would help.

If you do want the incremented date in A1 of each worksheet, use this macro

Sub Date_Increment()
'increment a date in A1 across sheets
Dim myDate As Date
Dim iCtr As Long
myDate = DateSerial(2007, 7, 1)
For iCtr = 1 To Worksheets.Count
With Worksheets(iCtr).Range("A1")
.Value = myDate - 1 + iCtr
.NumberFormat = "mm-dd-yyyy"
End With
Next iCtr
End Sub

Gord

On Sun, 24 Jun 2007 16:24:59 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Jack

You have changed the rules on us.

We thought you wanted a pre-entered date from each sheet's A1 to be the sheet name.

Try this from Chip Pearson. Doesn't need a date in A1 and assumes you have 31
sheets, not 32.

Sub NameSheets()
'Chip Pearson Feb 14th, 2007
Dim Ndx As Long
Dim StartMonth As Variant
StartMonth = Application.InputBox(Prompt:="Enter the month number.", Type:=1)
If StartMonth = False Then
Exit Sub
End If
For Ndx = 1 To ActiveWorkbook.Worksheets.Count
ActiveWorkbook.Worksheets(Ndx).Name = Format(DateSerial( _
IIf(StartMonth = 1, Year(Now) + 1, Year(Now)), StartMonth, Ndx), _
"dd mmm yyyy")
Next Ndx
End Sub


Gord

On Sun, 24 Jun 2007 16:15:01 -0700, JackR
wrote:

Sorry, I realize wont work is not descriptive.

I changed the macro to read as follows:

Sub namesheetfromcell()
ActiveSheet.Name = Format(Range("a1"), "mmm-dd")
End Sub

THis way it would only change one worksheet at a time, what I was trying to
accomplish is this;

I have 32 tabs in my workbook, Sheet 1 (tab 1) is where I would like to
input a date say July 1, I then want a macro to run that will fill each tab
name starting with the tab 2 to tab 31 with july, 1, july 2, etc.

hopefully this makes sense and you could help me figure this out as I am
stumped right now.

Thanks
"Gord Dibben" wrote:

Since "won't work" is none too descriptive, I will assume that your macro throws
an error.

Ususally due to the illegal / marks in a date.

Try this macro.

Sub wsname()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Name = Format((ws.Cells(1, 1).Value), "mmm dd yyyy")
Next ws
End Sub


Gord Dibben MS Excel MVP

On Sun, 24 Jun 2007 14:58:02 -0700, JackR
wrote:

I am using this macro for naming my tabs with the content of cell A1 on each
worksheet, my problem is that it wont work with a date, and I need to have a
date in cell A! to name each tab with that date. Any ideas would help.

THhis works perfect, except I need to have one sheet, which is the master
where I have look-up information for all other sheets, so I need the macro to
start on sheet 2, that is why I have the 32 sheets. Is there a way to add
something to the macro that would still run it but exclude a given sheet name
i.e. MASTER.

Thanks again for your help

"Gord Dibben" wrote:

Jack

You have changed the rules on us.

We thought you wanted a pre-entered date from each sheet's A1.

Try this from Chip Pearson. Doesn't need a date in A1 and assumes you have 31
sheets, not 32.

Sub NameSheets()
'Chip Pearson Feb 14th, 2007
Dim Ndx As Long
Dim StartMonth As Variant
StartMonth = Application.InputBox(Prompt:="Enter the month number.", Type:=1)
If StartMonth = False Then
Exit Sub
End If
For Ndx = 1 To ActiveWorkbook.Worksheets.Count
ActiveWorkbook.Worksheets(Ndx).Name = Format(DateSerial( _
IIf(StartMonth = 1, Year(Now) + 1, Year(Now)), StartMonth, Ndx), _
"dd mmm yyyy")
Next Ndx
End Sub


Gord

On Sun, 24 Jun 2007 16:15:01 -0700, JackR
wrote:

Sorry, I realize wont work is not descriptive.

I changed the macro to read as follows:

Sub namesheetfromcell()
ActiveSheet.Name = Format(Range("a1"), "mmm-dd")
End Sub

THis way it would only change one worksheet at a time, what I was trying to
accomplish is this;

I have 32 tabs in my workbook, Sheet 1 (tab 1) is where I would like to
input a date say July 1, I then want a macro to run that will fill each tab
name starting with the tab 2 to tab 31 with july, 1, july 2, etc.

hopefully this makes sense and you could help me figure this out as I am
stumped right now.

Thanks
"Gord Dibben" wrote:

Since "won't work" is none too descriptive, I will assume that your macro throws
an error.

Ususally due to the illegal / marks in a date.

Try this macro.

Sub wsname()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Name = Format((ws.Cells(1, 1).Value), "mmm dd yyyy")
Next ws
End Sub


Gord Dibben MS Excel MVP

On Sun, 24 Jun 2007 14:58:02 -0700, JackR
wrote:

I am using this macro for naming my tabs with the content of cell A1 on each
worksheet, my problem is that it wont work with a date, and I need to have a
date in cell A! to name each tab with that date. Any ideas would help.

Revised.

Select Sheet2 and SHIFT + click on sheet 32

Run this macro.

Sub NameSheets()
'Chip Pearson Feb 14th, 2007
Dim Ndx As Long
Dim StartMonth As Variant
StartMonth = Application.InputBox(Prompt:="Enter the month number.", Type:=1)
If StartMonth = False Then
Exit Sub
End If
For Ndx = 1 To ActiveWindow.SelectedSheets.Count
ActiveWindow.SelectedSheets(Ndx).Name = Format(DateSerial( _
IIf(StartMonth = 1, Year(Now) + 1, Year(Now)), StartMonth, Ndx), _
"dd mmm yyyy")
Next Ndx
End Sub

Gord

On Sun, 24 Jun 2007 16:46:00 -0700, JackR
wrote:

THhis works perfect, except I need to have one sheet, which is the master
where I have look-up information for all other sheets, so I need the macro to
start on sheet 2, that is why I have the 32 sheets. Is there a way to add
something to the macro that would still run it but exclude a given sheet name
i.e. MASTER.

Thanks again for your help

"Gord Dibben" wrote:

Jack

You have changed the rules on us.

We thought you wanted a pre-entered date from each sheet's A1.

Try this from Chip Pearson. Doesn't need a date in A1 and assumes you have 31
sheets, not 32.

Sub NameSheets()
'Chip Pearson Feb 14th, 2007
Dim Ndx As Long
Dim StartMonth As Variant
StartMonth = Application.InputBox(Prompt:="Enter the month number.", Type:=1)
If StartMonth = False Then
Exit Sub
End If
For Ndx = 1 To ActiveWorkbook.Worksheets.Count
ActiveWorkbook.Worksheets(Ndx).Name = Format(DateSerial( _
IIf(StartMonth = 1, Year(Now) + 1, Year(Now)), StartMonth, Ndx), _
"dd mmm yyyy")
Next Ndx
End Sub


Gord

On Sun, 24 Jun 2007 16:15:01 -0700, JackR
wrote:

Sorry, I realize wont work is not descriptive.

I changed the macro to read as follows:

Sub namesheetfromcell()
ActiveSheet.Name = Format(Range("a1"), "mmm-dd")
End Sub

THis way it would only change one worksheet at a time, what I was trying to
accomplish is this;

I have 32 tabs in my workbook, Sheet 1 (tab 1) is where I would like to
input a date say July 1, I then want a macro to run that will fill each tab
name starting with the tab 2 to tab 31 with july, 1, july 2, etc.

hopefully this makes sense and you could help me figure this out as I am
stumped right now.

Thanks
"Gord Dibben" wrote:

Since "won't work" is none too descriptive, I will assume that your macro throws
an error.

Ususally due to the illegal / marks in a date.

Try this macro.

Sub wsname()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Name = Format((ws.Cells(1, 1).Value), "mmm dd yyyy")
Next ws
End Sub


Gord Dibben MS Excel MVP

On Sun, 24 Jun 2007 14:58:02 -0700, JackR
wrote:

I am using this macro for naming my tabs with the content of cell A1 on each
worksheet, my problem is that it wont work with a date, and I need to have a
date in cell A! to name each tab with that date. Any ideas would help.

Thank you, that will work. I appreciate your time and patience.

"Gord Dibben" wrote:

Revised.

Select Sheet2 and SHIFT + click on sheet 32

Run this macro.

Sub NameSheets()
'Chip Pearson Feb 14th, 2007
Dim Ndx As Long
Dim StartMonth As Variant
StartMonth = Application.InputBox(Prompt:="Enter the month number.", Type:=1)
If StartMonth = False Then
Exit Sub
End If
For Ndx = 1 To ActiveWindow.SelectedSheets.Count
ActiveWindow.SelectedSheets(Ndx).Name = Format(DateSerial( _
IIf(StartMonth = 1, Year(Now) + 1, Year(Now)), StartMonth, Ndx), _
"dd mmm yyyy")
Next Ndx
End Sub

Gord

On Sun, 24 Jun 2007 16:46:00 -0700, JackR
wrote:

THhis works perfect, except I need to have one sheet, which is the master
where I have look-up information for all other sheets, so I need the macro to
start on sheet 2, that is why I have the 32 sheets. Is there a way to add
something to the macro that would still run it but exclude a given sheet name
i.e. MASTER.

Thanks again for your help

"Gord Dibben" wrote:

Jack

You have changed the rules on us.

We thought you wanted a pre-entered date from each sheet's A1.

Try this from Chip Pearson. Doesn't need a date in A1 and assumes you have 31
sheets, not 32.

Sub NameSheets()
'Chip Pearson Feb 14th, 2007
Dim Ndx As Long
Dim StartMonth As Variant
StartMonth = Application.InputBox(Prompt:="Enter the month number.", Type:=1)
If StartMonth = False Then
Exit Sub
End If
For Ndx = 1 To ActiveWorkbook.Worksheets.Count
ActiveWorkbook.Worksheets(Ndx).Name = Format(DateSerial( _
IIf(StartMonth = 1, Year(Now) + 1, Year(Now)), StartMonth, Ndx), _
"dd mmm yyyy")
Next Ndx
End Sub


Gord

On Sun, 24 Jun 2007 16:15:01 -0700, JackR
wrote:

Sorry, I realize wont work is not descriptive.

I changed the macro to read as follows:

Sub namesheetfromcell()
ActiveSheet.Name = Format(Range("a1"), "mmm-dd")
End Sub

THis way it would only change one worksheet at a time, what I was trying to
accomplish is this;

I have 32 tabs in my workbook, Sheet 1 (tab 1) is where I would like to
input a date say July 1, I then want a macro to run that will fill each tab
name starting with the tab 2 to tab 31 with july, 1, july 2, etc.

hopefully this makes sense and you could help me figure this out as I am
stumped right now.

Thanks
"Gord Dibben" wrote:

Since "won't work" is none too descriptive, I will assume that your macro throws
an error.

Ususally due to the illegal / marks in a date.

Try this macro.

Sub wsname()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Name = Format((ws.Cells(1, 1).Value), "mmm dd yyyy")
Next ws
End Sub


Gord Dibben MS Excel MVP

On Sun, 24 Jun 2007 14:58:02 -0700, JackR
wrote:

I am using this macro for naming my tabs with the content of cell A1 on each
worksheet, my problem is that it wont work with a date, and I need to have a
date in cell A! to name each tab with that date. Any ideas would help.