rename excel tabs months for year without doing it individually

[Vicky P]

I want to rename the tabs of a new work book with the months of the year jan
09 to dec 09 without having to do it individually.
I'm sure it can be done by select all tabs - but then what??

 

[Wigi]

If you don't want to do it manually (what the heck... 12 stupid changes),
then you need VBA code for it.

 

[Vicky P]

Wigi - thank you for answering
it is not so much that I can't rename 12 tabs manually - but I am such a
baby at this and I really do want to learn "how to do stuff"
In the future i may need to rename 365 tabs - then it would'nt be so easy -
so start small.
I've learnt such a lot - and its always a joy when you find something out
that makes life easier / quicker
I have opened VBA code - but what do you do when you are there??

 

[Sheeloo]

To learn use
Sub RenameTabs()
' Renames all worksheet tabs with each worksheet's cell A1 contents.
'If cell A1 has no content, then that tab is not renamed.

For i = 1 To Sheets.Count
If Worksheets(i).Range("A1").Value <> "" Then
Sheets(i).Name = Worksheets(i).Range("A1").Value
End If
Next

End Sub

Source:
http://it.toolbox.com/wiki/index.php/Excel_Macro_to_Rename_Worksheet_Tabs

 

[Gord Dibben]

Couple more.

Type January-2009 in A1 of sheet1

Copy down to A12 then run macro.

Sub NameWS()
'name sheets with list in A1:A12 on first sheet
On Error Resume Next
For i = 1 To Worksheets.Count
Sheets(i).Name = Sheets(1).Cells(i, 1).Value
Next i
End Sub

The next macro will rename existing sheets to month and day

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

Maybe you want to add new sheets for a month

Sub Add_Sheets()
For i = 31 To 1 Step -1
Worksheets.Add.Name = "October " & i
Next
End Sub


Gord Dibben MS Excel MVP