Tabs

[Lois]

Hi
When setting up spreadsheets in work, the tabs are always named the months
of the year. Is there any quick way of naming tabs in a workbook with a
'default' name so they dont have to be continously typed onto each workbook?

 

[Mike H]

Hi,

You could rune this simple macro. Right click any sheet tab, view code and
paste the code in.

Sub Name_Sheets()
On Error Resume Next
For i = 1 To 12
Sheets(i).Name = MonthName(i)
Next
End Sub

Mike

 

[Jacob Skaria]

Please create 12 sheets or set the number of default sheets to 12 before
running this maco.

If this post helps click Yes

 

[Lois]

Great!! that saves me a lot of time! is there any way 12 sheets can
automativally be inserted into a workbook as oppose to 3? that way i dont
even have to insert the 12 tabs!!

 

[Mike H]

Hi,

You could add the sheets with code but you can also do it using but that
will affect all workbooks you open. Try this modified code

Sub Name_Sheets()
numsheets = Worksheets.Count
For i = 1 To 12
If i > numsheets Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = MonthName(i)
Else
Sheets(i).Name = MonthName(i)
End If
Next
End Sub

Mike

 

[Jacob Skaria]

Sub CreateMonthSheets()
Dim intTemp
ActiveWorkbook.Sheets.Add Count:=(12 - ActiveWorkbook.Sheets.Count)
For intTemp = 1 To ActiveWorkbook.Sheets.Count
ActiveWorkbook.Sheets(intTemp).Name = MonthName(intTemp)
Next
End Sub

 

[Mike H]

Lois posted this in the wrong sub thread

Hi,

You could add the sheets with code but you can also do it using but that
will affect all workbooks you open. Try this modified code

Sub Name_Sheets()
numsheets = Worksheets.Count
For i = 1 To 12
If i > numsheets Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = MonthName(i)
Else
Sheets(i).Name = MonthName(i)
End If
Next
End Sub

 

[Lois]

Great thanks
--
Lois

Lois posted this in the wrong sub thread

Hi,

You could add the sheets with code but you can also do it using but that
will affect all workbooks you open. Try this modified code

Sub Name_Sheets()
numsheets = Worksheets.Count
For i = 1 To 12
If i > numsheets Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = MonthName(i)
Else
Sheets(i).Name = MonthName(i)
End If
Next
End Sub

 

[Gord Dibben]

I would suggest creating a Template(*.xlt) file with all you customizations
including 12 sheets with the month names etc.

Use that Template as the basis for all workbooks.

No need for macros.

See help on Templates for more info.


Gord Dibben MS Excel MVP