Name worksheet tabs with a date in a cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
ws.Name = format(ws.Cells(1, 1).Value, "mmm-d")


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
Click to expand...
 
If you already formatted A1 to show what you want, you could use this, too:

ws.Name = ws.Cells(1, 1).Text
 
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
 
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
 
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
 
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
 
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
 
Back
Top