Fill list on sheet names

  • Thread starter Thread starter Ray
  • Start date Start date
R

Ray

I would like to create a workbook with 12 sheets. I want
to name the sheets using the month of the year. Is it
possible to automate the renaming of sheets? I was
thinking of something like Edit>Fill>Series.
TIA
Ray
 
AFAIK, you can only do it by macro. One way:

Public Sub MonthifySheetNames()
Dim wkSht As Worksheet
On Error Resume Next 'ignore sheets beyond 12
For Each wkSht In Worksheets
With wkSht
.Name = Application.GetCustomListContents(4)(.Index)
End With
Next wkSht
On Error GoTo 0
End Sub
 
Try this

It will add a new workbook with 12 sheets and name the sheets

Sub test()
Dim nwb As Workbook
Dim ShinWB As Integer
Dim newShinWB As Integer
Dim a As Integer
ShinWB = Application.SheetsInNewWorkbook
newShinWB = 12
Application.SheetsInNewWorkbook = newShinWB
Set nwb = Workbooks.Add
Application.SheetsInNewWorkbook = ShinWB
For a = 1 To 12
nwb.Sheets(a).Name = Format(DateSerial(2003, a, 1), "mmm")
Next
End Sub
 
try
Sub nameshts()
On Error Resume Next
ctr = 1
For Each ws In Worksheets
mydate = Format(DateSerial(2004, ctr, 1), "MMM")
ws.Name = mydate
ctr = ctr + 1
Next
End Sub
 
Back
Top