macro to create a worksheet for every name on a list?

[Guest]

I am making worksheets for each name on a list and would like to have a macro
that can do that. I created an example sheet to copy and I recorded this
macro but I don't know how to select names from a defined list and to get it
to keep creating new sheets for each name.

Can someone help?

TIA


Todd


Sub CreateSheets()

Sheets("ExampleSheet").Select
Sheets("ExampleSheet").Copy Before:=Sheets(18)
Sheets("ExampleSheet (2)").Select
Range("C21").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Supplier List").Select
Range("A25").Select
Selection.Copy
Sheets("ExampleSheet (2)").Select
Sheets("ExampleSheet (2)").Name = "Cutler Hammer "
Range("D20").Select
End Sub

 

[Bob Phillips]

Assuming the list is in A1:A10

For Each cell In range("A1:A10")
Worrksheets.Add(After:=Worksheets.Count).Name = cell.Value
Next cell

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Sub CreateSheets()
Dim cell As Range, rng As Range
With Worksheets("Supplier List")
Set rng = .Range(.Range("A25"), .Range("A25").End(xlDown))
End With
For Each cell In rng
Sheets("ExampleSheet").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = cell.Value
Next
End Sub

 

[keepITcool]

Bob..

I thought.. HEY s'thing new.. but alas it's a typo.

s/b

Sub foo()
Dim cell
For Each cell In Range("A1:A10")
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = cell.Value
Next cell

End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Bob Phillips wrote :

 

[Guest]

Thank you! I have been using it. It does creates a second example sheet,
example sheet(2) and the debugger kicks in because of two sheets having the
same name. But it works.

Todd

 

[Bob Phillips]

Hi Jurgen,

As Harlan keeps telling me, we very little new here !

Bob

 

[keepITcool]

I just MAY have found something new...

get a chart to accept a formula in a dataseries??


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Bob Phillips wrote :

 

[Eric Barber]

I am making worksheets for each name on a list and would like to have a macro
that can do that.

is this a partial answer?

Sub makeMore()
Dim sRptItem As String, vCell As Variant

Application.ScreenUpdating = False
If ActiveSheet.Name = "Report" Then
For Each vCell In Selection
If vCell.Value <> "" And Len(vCell.Value) < 31 Then
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = vCell.Value
' other stuff to be done on sheet
End If
Next vCell
Else
MsgBox "must be run from ""Report"" sheet with a range of
cells containing the names of new sheets selected"
End If
Application.ScreenUpdating = True
End Sub

 

[Guest]

Hope you guys are still following this. The macro stops creating sheets
somewhere in the list and recreates an examplesheet. Then it stops due to a
bad name (its examplesheet (2)). I thought I could work around it but I
can't. Any ideas?

Todd.


ps thanks for all the responses.

 

[Tom Ogilvy]

Might want to check John Green's book, although I don't recall *exactly*
what you posted so I could be wrong.

 

[keepITcool]

re a question on calling a UDF direct from graph's dataseries
i found that it could be done via a name.

it appears to have been thought of by others, which just proved
Bob's/Harlan's point...

very little new here !

 

[aking1987]

Hi, I have a similar enquiry...

BUt the opposite way round.

I can create the worksheets, but want the worksheet names to list i
cells on a master worksheet.

How do i do this

 

[R.VENKATARAMAN]

try this code
your list is in SHEET1 and in range D4 to D8


Option Explicit


Public Sub test()
Dim nname As String
Dim cell As Range
Worksheets("sheet1").Activate
For Each cell In Range("d4:d8")
cell.Activate
nname = ActiveCell.Value
Worksheets.Add after:=Sheet1

ActiveSheet.Name = nname
Worksheets("sheet1").Activate

Next

End Sub

 

[aking1987]

Application-defined or object-defined error


Code
-------------------
Option Explicit


Public Sub test()
Dim nname As String
Dim cell As Range
Worksheets("Master Numbers").Activate
For Each cell In Range("d4:d8")
cell.Activate
nname = ActiveCell.Value
Worksheets.Add after:=Sheet1

ActiveSheet.Name = nname

Worksheets("Master Numbers").Activate

Next

End Sub

 

[aking1987]

The list of worksheet names is continually growing...

D4:d8 only gives space for 4 names.

can it be done by column? So it can continue to grow infintly

Thank you

 

[keepITcool]

try s'th like:

dim nm as name
dim n as long

With Worksheets("Master").Cells(1)
for each nm in activeworkbook.names
.offset(n).resize(,2) =
array(nm.name,nm.refersto)
n=n+1
next
end with




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


aking1987 wrote :