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.