a quick way of inserting and naming multiple worksheets in Excel?

[Sue]

We have to insert 20 worksheets that each have a different name into an
existing Excel Workbook - is there a quick way of doing this?

Please help if you can

Sue

 

[JLatham]

This code should work for you, it inserts 20 sheets at the end of the active
workbook and gives them names you define. To use the code, open the workbook
and press [Alt]+[F11] to open the VB Editor. Choose Insert --> Module and
copy and paste the code into it, change the worksheet names to whatever you
want them to be (cannot be the name of an existing sheet in the book). Close
the VB Editor and use Tools --> Macro --> Macros and run the macro. Actually
you could just press [F5] while in the VB Editor with the cursor inside of
the code and then just delete the code once you're done with it.

Here's that code, more to follow:
Sub Add20Sheets()
'Adds and renames 20 sheets at the end
'of the workbook
'set up names for the new sheets
Dim newNames(1 To 20) As String
Dim LC As Integer ' loop counter

'change these as required
newNames(1) = "NewSheet#1"
newNames(2) = "NewSheet#2"
newNames(3) = "NewSheet#3"
newNames(4) = "NewSheet#4"
newNames(5) = "NewSheet#5"
newNames(6) = "NewSheet#6"
newNames(7) = "NewSheet#7"
newNames(8) = "NewSheet#8"
newNames(9) = "NewSheet#9"
newNames(10) = "NewSheet#10"
newNames(11) = "NewSheet#11"
newNames(12) = "NewSheet#12"
newNames(13) = "NewSheet#13"
newNames(14) = "NewSheet#14"
newNames(15) = "NewSheet#15"
newNames(16) = "NewSheet#16"
newNames(17) = "NewSheet#17"
newNames(18) = "NewSheet#18"
newNames(19) = "NewSheet#19"
newNames(20) = "NewSheet#20"
'we loop based on the size of the
'newNames() array, so you can change
'that to modify this to work to add
'different number of worksheets
'in the future
Application.ScreenUpdating = False ' speed things up
For LC = LBound(newNames) To UBound(newNames)
Sheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = newNames(LC)
Next
End Sub

If you just want to add 20 sheets and let Excel give them names, use this
code instead:

Sub Add20Sheets()
'Adds 20 sheets at the end
'of the workbook
Application.ScreenUpdating = False ' speed things up
For LC = 1 To 20
Sheets.Add after:=Worksheets(Worksheets.Count)
Next
End Sub

 

[JLatham]

Oops, cut too much away from the 2nd macro: use this instead for the "just
20 sheets" solution:

Sub Add20Sheets()
'Adds 20 sheets at the end
'of the workbook
Dim LC As Integer
Application.ScreenUpdating = False ' speed things up
For LC = 1 To 20
Sheets.Add after:=Worksheets(Worksheets.Count)
Next
End Sub

 

[Jarek Kujawa]

with yr new worksheet names in A1:A20 try this macro

Sub cus()

For Each cell In Selection
Worksheets.Add
ActiveSheet.Name = cell
Next cell

End Sub

 

[Jarek Kujawa]

forgot to add: select A1:A20

with yr new worksheet names in A1:A20 try this macro

Sub cus()

For Each cell In Selection
Worksheets.Add
ActiveSheet.Name = cell
Next cell

End Sub





- Poka¿ cytowany tekst -

 

[Gord Dibben]

With a list of names in A1:A20 on a current sheet.

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


Gord Dibben MS Excel MVP