Autofill Worksheets / Worksheet Names

H

hawaiiham2000

Is it possible to use an autofill type feature to add and name worksheets in
a series i.e. Jan, Feb, Mar, etc. or 1, 2, 3?

Thanks!
 
L

Luke M

Not directly in XL, but you could do it with a short macro. Let's say your
list in in cells a1:a12 (must start in first row!). With the sheet containing
these values active, run this macro:

'===============
Sub NameSheets()
i = 1
For Each ws In ThisWorkbook.Sheets
ws.Name = ActiveSheet.Cells(i, "A").Value
i = i + 1
Next ws
End Sub
'==============

You can install a macro by opening the VBE (Alt+F11), Insert - Module, paste
the code in. From XL, go to Tools - Macro - Macros.
 
G

Gord Dibben

You want to add more sheets and name them?

In Sheet1 enter the list of Jan to Dec in A1:A12

Run this macro from Sheet1

Sub Add_Sheets22()
Dim rCell As Range
For Each rCell In Range("A1:A12")
With Worksheets.Add(After:=Worksheets(Worksheets.Count))
.Name = rCell.Value
End With
Next rCell
End Sub

You want to add 20 sheets named 1 to 20

Sub Add_Sheets()
For i = 20 To 1 Step -1
Worksheets.Add.Name = i
Next
End Sub

Many other sheet naming macros.........days of month.......weeks of month
etc.


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top