Add Worksheets

[DB100]

Hi Guys

Nice easy one ( I hope )

Is it possible to dictate the amount of worksheets to add as a fomula
rather than having to have a list of "Sheets.Add" 15 times

I Have a list of names ( A3:A19) that need to have a worksheet create
and named after them.

So I need the macro to calulate how many names there are in the list
It changes ) and then to create a worksheet for each and assign it tha
name

thanks

Davi

 

[papou]

Hi

For i = 3 To Worksheets("YourSheet").Range("A65536").end(xlup).row
sheets.add
activesheet.name= worksheets("YourSheet").cells(i,1)
Next i

HTH
Cordially
Pascal

 

[Chip Pearson]

You can specify the number of worksheets to add using the Count
argument to the Add method. E.g.,

Worksheets.Add Count:=15

but this will give them default names. If you need to assign
names to the worksheets, use something like

Dim Rng As Range
Dim ListRng As Range
Set ListRng = Range(Range("A3"), Range("A3").End(xlDown))
For Each Rng In ListRng
If Rng.Text <> "" Then
With Worksheets
.Add(after:=.Item(.Count)).Name = Rng.Text
End With
End If
Next Rng


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com