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 Befo=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

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)


"Todd" wrote in message
...
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 Befo=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

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

--
Regards,
Tom Ogilvy

"Todd" wrote in message
...
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 Befo=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..

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 :

Assuming the list is in A1:A10

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

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



"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Todd" wrote in message
...
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 Befo=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

Hi Jurgen,

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

Bob

"keepITcool" wrote in message
ft.com...
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 :

Assuming the list is in A1:A10

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

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 :

Hi Jurgen,

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

Bob

"keepITcool" wrote in message
ft.com...
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 :

Assuming the list is in A1:A10

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

"Todd" wrote in message ...
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

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.



"Eric Barber" wrote:

"Todd" wrote in message ...
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

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

--
Regards,
Tom Ogilvy

"keepITcool" wrote in message
ft.com...
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 :

Hi Jurgen,

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

Bob

"keepITcool" wrote in message
ft.com...
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 :

Assuming the list is in A1:A10

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

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 :-)!


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

Tom Ogilvy wrote in message
:

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

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

--
aking198
-----------------------------------------------------------------------
aking1987's Profile: http://www.excelforum.com/member.php...fo&userid=1539
View this thread: http://www.excelforum.com/showthread.php?threadid=27758

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 wrote in message
...

Hi, I have a similar enquiry...

BUt the opposite way round.

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

How do i do this?


--
aking1987
------------------------------------------------------------------------
aking1987's Profile:
http://www.excelforum.com/member.php...o&userid=15393
View this thread: http://www.excelforum.com/showthread...hreadid=277583

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

-------------------

--
aking198
-----------------------------------------------------------------------
aking1987's Profile: http://www.excelforum.com/member.php...fo&userid=1539
View this thread: http://www.excelforum.com/showthread.php?threadid=27758

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

--
aking198
-----------------------------------------------------------------------
aking1987's Profile: http://www.excelforum.com/member.php...fo&userid=1539
View this thread: http://www.excelforum.com/showthread.php?threadid=27758

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 :


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!