Excel - rename excel tabs months for year without doing it individually

Asked By Vicky on 07-Oct-08 04:57 PM
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 am sure it can be done by select all tabs - but then what??




Wig replied on 07-Oct-08 05:46 PM
If you do not want to do it manually (what the heck... 12 stupid changes),
then you need VBA code for it.


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music
Vicky replied on 07-Oct-08 07:20 PM
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??
to_sheelo replied on 07-Oct-08 08:05 PM
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 replied on 08-Oct-08 02:09 PM
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), _
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

On Tue, 7 Oct 2008 17:05:07 -0700, Sheeloo