Hi Bob,

I've incorporated your code successfully, but names are being continuously
added to my list (C12 to C412) from which my worksheet names are being
generated. I'd like to add an error handling event to your code so that the
user can add names once they've run the macro. For example: After running the
macro to add 195 names (known at present), I need to add an error handling
procedure to have VB loop through names until it comes across a newly added
name(s) (known in future) at which point it will create a new worksheet with
the corresponding name(s) in my "C12:C412" range using the template in
"Master" without generating an error message. To add to this problem, I would
like to add hyperlinks to each of my cells (C12 to C412) so that the user can
quickly go to a requested worksheet.

Here is the code that I have thus far:

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "C").End(xlUp)
Set Rng = WS.Range("C12", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
End If
Next
End Sub

Your or someone else's assistance would be greatly appreciated! - TIA
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
H: (613) 907-1211
W: (613) 943-9098

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet, sh as Worksheet, s as String
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "C").End(xlUp)
Set Rng = WS.Range("C12", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
on Error Resume Next
set sh = worksheets(cell.Value)
on Error goto 0
if sh is nothing then
Application.ScreenUpdating = False
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
s = ActiveSheet.Name & "!A1"
WS.Hyperlinks.Add Anchor:=cell, Address:="", _
SubAddress:=s, TextToDisplay:=ActiveSheet.Name
ws.activate
Application.ScreenUpdating = True
end if
End If
Next

End Sub

if you sheet names have spaces in them, then it may need modification.

--
Regards,
Tom Ogilvy


"klysell" wrote:

Hi Bob,

I've incorporated your code successfully, but names are being continuously
added to my list (C12 to C412) from which my worksheet names are being
generated. I'd like to add an error handling event to your code so that the
user can add names once they've run the macro. For example: After running the
macro to add 195 names (known at present), I need to add an error handling
procedure to have VB loop through names until it comes across a newly added
name(s) (known in future) at which point it will create a new worksheet with
the corresponding name(s) in my "C12:C412" range using the template in
"Master" without generating an error message. To add to this problem, I would
like to add hyperlinks to each of my cells (C12 to C412) so that the user can
quickly go to a requested worksheet.

Here is the code that I have thus far:

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "C").End(xlUp)
Set Rng = WS.Range("C12", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
End If
Next
End Sub

Your or someone else's assistance would be greatly appreciated! - TIA
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
H: (613) 907-1211
W: (613) 943-9098

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "C").End(xlUp)
Set Rng = WS.Range("C12", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Set WS = Nothing
On Error Resume Next
Set WS = Worksheets(cell.Value)
On Error GoTo 0
If WS Is Nothing Then
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
End If
End If
Next
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"klysell" .(donotspam) wrote in message
...
Hi Bob,

I've incorporated your code successfully, but names are being continuously
added to my list (C12 to C412) from which my worksheet names are being
generated. I'd like to add an error handling event to your code so that
the
user can add names once they've run the macro. For example: After running
the
macro to add 195 names (known at present), I need to add an error handling
procedure to have VB loop through names until it comes across a newly
added
name(s) (known in future) at which point it will create a new worksheet
with
the corresponding name(s) in my "C12:C412" range using the template in
"Master" without generating an error message. To add to this problem, I
would
like to add hyperlinks to each of my cells (C12 to C412) so that the user
can
quickly go to a requested worksheet.

Here is the code that I have thus far:

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "C").End(xlUp)
Set Rng = WS.Range("C12", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
End If
Next
End Sub

Your or someone else's assistance would be greatly appreciated! - TIA
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
H: (613) 907-1211
W: (613) 943-9098

This should do it... There are the usual restriction on the sheet name in
terms of the length of the name and you can not name the sheet "History" but
otherwise it should work... You should add validation to the rnage C12:C?? to
ensure valid sheet names...

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "C").End(xlUp)
Set Rng = WS.Range("C12", LastCell)
For Each cell In Rng
If Not SheetExists(cell.Value) And Not IsEmpty(cell) Then
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
cell.Hyperlinks.Add Anchor:=cell, _
Address:="", _
SubAddress:="'" & ActiveSheet.Name & "'!A1", _
TextToDisplay:=cell.Value
End If
Next
End Sub

Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function
--
HTH...

Jim Thomlinson


"klysell" wrote:

Hi Bob,

I've incorporated your code successfully, but names are being continuously
added to my list (C12 to C412) from which my worksheet names are being
generated. I'd like to add an error handling event to your code so that the
user can add names once they've run the macro. For example: After running the
macro to add 195 names (known at present), I need to add an error handling
procedure to have VB loop through names until it comes across a newly added
name(s) (known in future) at which point it will create a new worksheet with
the corresponding name(s) in my "C12:C412" range using the template in
"Master" without generating an error message. To add to this problem, I would
like to add hyperlinks to each of my cells (C12 to C412) so that the user can
quickly go to a requested worksheet.

Here is the code that I have thus far:

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "C").End(xlUp)
Set Rng = WS.Range("C12", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
End If
Next
End Sub

Your or someone else's assistance would be greatly appreciated! - TIA
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
H: (613) 907-1211
W: (613) 943-9098

Thanks guys!

Hey, do you know what course to take or book to buy to learn VBA programming
for Excel? I already know how to use Excel tools proficiently but would like
to focus on expert VBA programming.

Cheers!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211



"Bob Phillips" wrote:

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "C").End(xlUp)
Set Rng = WS.Range("C12", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Set WS = Nothing
On Error Resume Next
Set WS = Worksheets(cell.Value)
On Error GoTo 0
If WS Is Nothing Then
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
End If
End If
Next
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"klysell" .(donotspam) wrote in message
...
Hi Bob,

I've incorporated your code successfully, but names are being continuously
added to my list (C12 to C412) from which my worksheet names are being
generated. I'd like to add an error handling event to your code so that
the
user can add names once they've run the macro. For example: After running
the
macro to add 195 names (known at present), I need to add an error handling
procedure to have VB loop through names until it comes across a newly
added
name(s) (known in future) at which point it will create a new worksheet
with
the corresponding name(s) in my "C12:C412" range using the template in
"Master" without generating an error message. To add to this problem, I
would
like to add hyperlinks to each of my cells (C12 to C412) so that the user
can
quickly go to a requested worksheet.

Here is the code that I have thus far:

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "C").End(xlUp)
Set Rng = WS.Range("C12", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
End If
Next
End Sub

Your or someone else's assistance would be greatly appreciated! - TIA
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
H: (613) 907-1211
W: (613) 943-9098

http://www.j-walk.com/ss/books/index.htm

look at the power programming series for the version of interest.


Stephen Bullen et al
http://www.oaltd.co.uk/

see the book icons/links on the left
--
Regards,
Tom Ogilvy


"klysell" wrote:

Thanks guys!

Hey, do you know what course to take or book to buy to learn VBA programming
for Excel? I already know how to use Excel tools proficiently but would like
to focus on expert VBA programming.

Cheers!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211



"Bob Phillips" wrote:

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "C").End(xlUp)
Set Rng = WS.Range("C12", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Set WS = Nothing
On Error Resume Next
Set WS = Worksheets(cell.Value)
On Error GoTo 0
If WS Is Nothing Then
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
End If
End If
Next
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"klysell" .(donotspam) wrote in message
...
Hi Bob,

I've incorporated your code successfully, but names are being continuously
added to my list (C12 to C412) from which my worksheet names are being
generated. I'd like to add an error handling event to your code so that
the
user can add names once they've run the macro. For example: After running
the
macro to add 195 names (known at present), I need to add an error handling
procedure to have VB loop through names until it comes across a newly
added
name(s) (known in future) at which point it will create a new worksheet
with
the corresponding name(s) in my "C12:C412" range using the template in
"Master" without generating an error message. To add to this problem, I
would
like to add hyperlinks to each of my cells (C12 to C412) so that the user
can
quickly go to a requested worksheet.

Here is the code that I have thus far:

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "C").End(xlUp)
Set Rng = WS.Range("C12", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
End If
Next
End Sub

Your or someone else's assistance would be greatly appreciated! - TIA
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
H: (613) 907-1211
W: (613) 943-9098

Hi Guys,

Now that I've had time to try each solution, all three are giving me the
same error message: "Copy method of worksheet class failed". Upon choosing
to debug, this is the code that is being flagged as erroneous:
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)

It's interesting, but Bob's code which worked before isn't working now and
is giving me the same error code.... I don't know what I could have done in
the interim for his code to not work now. My three current sheets a
"Current Blackbook", "Departments", "Summary", and "Master" (in this order).
This macro takes the list of names (all with space - i.e. "Lysell, Kent")
from C12:C211 on Summary worksheet and using the Master worksheet as a
template, propagates my workbook with 195 names.

Can anyone shed some light on this mystery? And by the way Tom, thanks for
the references for further reading. Although this isn't my field, I am
getting a kick out of this.

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211



"Tom Ogilvy" wrote:

http://www.j-walk.com/ss/books/index.htm

look at the power programming series for the version of interest.


Stephen Bullen et al
http://www.oaltd.co.uk/

see the book icons/links on the left
--
Regards,
Tom Ogilvy


"klysell" wrote:

Thanks guys!

Hey, do you know what course to take or book to buy to learn VBA programming
for Excel? I already know how to use Excel tools proficiently but would like
to focus on expert VBA programming.

Cheers!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211



"Bob Phillips" wrote:

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "C").End(xlUp)
Set Rng = WS.Range("C12", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Set WS = Nothing
On Error Resume Next
Set WS = Worksheets(cell.Value)
On Error GoTo 0
If WS Is Nothing Then
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
End If
End If
Next
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"klysell" .(donotspam) wrote in message
...
Hi Bob,

I've incorporated your code successfully, but names are being continuously
added to my list (C12 to C412) from which my worksheet names are being
generated. I'd like to add an error handling event to your code so that
the
user can add names once they've run the macro. For example: After running
the
macro to add 195 names (known at present), I need to add an error handling
procedure to have VB loop through names until it comes across a newly
added
name(s) (known in future) at which point it will create a new worksheet
with
the corresponding name(s) in my "C12:C412" range using the template in
"Master" without generating an error message. To add to this problem, I
would
like to add hyperlinks to each of my cells (C12 to C412) so that the user
can
quickly go to a requested worksheet.

Here is the code that I have thus far:

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "C").End(xlUp)
Set Rng = WS.Range("C12", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
End If
Next
End Sub

Your or someone else's assistance would be greatly appreciated! - TIA
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
H: (613) 907-1211
W: (613) 943-9098

Hi Tom,

Ok, I've got your code to work - the hyperlinks work great! The only thing
is that if I run the macro again with more names added to the list, the macro
doesn't initiate (doesn't even indicate an error message). I can't figure out
why the code isn't cycling through on successive attempts once names are
added....

Thanks in advance (and for Jim and Bob's help as well).
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211



"Tom Ogilvy" wrote:

http://www.j-walk.com/ss/books/index.htm

look at the power programming series for the version of interest.


Stephen Bullen et al
http://www.oaltd.co.uk/

see the book icons/links on the left
--
Regards,
Tom Ogilvy


"klysell" wrote:

Thanks guys!

Hey, do you know what course to take or book to buy to learn VBA programming
for Excel? I already know how to use Excel tools proficiently but would like
to focus on expert VBA programming.

Cheers!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211



"Bob Phillips" wrote:

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "C").End(xlUp)
Set Rng = WS.Range("C12", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Set WS = Nothing
On Error Resume Next
Set WS = Worksheets(cell.Value)
On Error GoTo 0
If WS Is Nothing Then
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
End If
End If
Next
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"klysell" .(donotspam) wrote in message
...
Hi Bob,

I've incorporated your code successfully, but names are being continuously
added to my list (C12 to C412) from which my worksheet names are being
generated. I'd like to add an error handling event to your code so that
the
user can add names once they've run the macro. For example: After running
the
macro to add 195 names (known at present), I need to add an error handling
procedure to have VB loop through names until it comes across a newly
added
name(s) (known in future) at which point it will create a new worksheet
with
the corresponding name(s) in my "C12:C412" range using the template in
"Master" without generating an error message. To add to this problem, I
would
like to add hyperlinks to each of my cells (C12 to C412) so that the user
can
quickly go to a requested worksheet.

Here is the code that I have thus far:

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "C").End(xlUp)
Set Rng = WS.Range("C12", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
End If
Next
End Sub

Your or someone else's assistance would be greatly appreciated! - TIA
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
H: (613) 907-1211
W: (613) 943-9098

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet, sh as Worksheet, s as String
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "C").End(xlUp)
Set Rng = WS.Range("C12", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Set sh = Nothing '<=== added line
on Error Resume Next
set sh = worksheets(cell.Value)
on Error goto 0
if sh is nothing then
Application.ScreenUpdating = False
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
s = ActiveSheet.Name & "!A1"
WS.Hyperlinks.Add Anchor:=cell, Address:="", _
SubAddress:=s, TextToDisplay:=ActiveSheet.Name
ws.activate
Application.ScreenUpdating = True
end if
End If
Next

End Sub

--
Regards,
Tom Ogilvy


"klysell" .(donotspam) wrote in message
...
Hi Tom,

Ok, I've got your code to work - the hyperlinks work great! The only thing
is that if I run the macro again with more names added to the list, the
macro
doesn't initiate (doesn't even indicate an error message). I can't figure
out
why the code isn't cycling through on successive attempts once names are
added....

Thanks in advance (and for Jim and Bob's help as well).
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211



"Tom Ogilvy" wrote:

http://www.j-walk.com/ss/books/index.htm

look at the power programming series for the version of interest.


Stephen Bullen et al
http://www.oaltd.co.uk/

see the book icons/links on the left
--
Regards,
Tom Ogilvy


"klysell" wrote:

Thanks guys!

Hey, do you know what course to take or book to buy to learn VBA
programming
for Excel? I already know how to use Excel tools proficiently but would
like
to focus on expert VBA programming.

Cheers!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211



"Bob Phillips" wrote:

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "C").End(xlUp)
Set Rng = WS.Range("C12", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Set WS = Nothing
On Error Resume Next
Set WS = Worksheets(cell.Value)
On Error GoTo 0
If WS Is Nothing Then
Sheets("Master").Copy
after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
End If
End If
Next
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"klysell" .(donotspam) wrote in message
...
Hi Bob,

I've incorporated your code successfully, but names are being
continuously
added to my list (C12 to C412) from which my worksheet names are
being
generated. I'd like to add an error handling event to your code so
that
the
user can add names once they've run the macro. For example: After
running
the
macro to add 195 names (known at present), I need to add an error
handling
procedure to have VB loop through names until it comes across a
newly
added
name(s) (known in future) at which point it will create a new
worksheet
with
the corresponding name(s) in my "C12:C412" range using the template
in
"Master" without generating an error message. To add to this
problem, I
would
like to add hyperlinks to each of my cells (C12 to C412) so that
the user
can
quickly go to a requested worksheet.

Here is the code that I have thus far:

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "C").End(xlUp)
Set Rng = WS.Range("C12", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Sheets("Master").Copy
after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
End If
Next
End Sub

Your or someone else's assistance would be greatly appreciated! -
TIA
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
H: (613) 907-1211
W: (613) 943-9098

Hi Tom,

Thanks! It works! But you were right about putting in names with spaces. For
some reason the hyperlinks only work with single-word names. Is there some
way get around this limitation?

Thanks again.
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211



"Tom Ogilvy" wrote:

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet, sh as Worksheet, s as String
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "C").End(xlUp)
Set Rng = WS.Range("C12", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Set sh = Nothing '<=== added line
on Error Resume Next
set sh = worksheets(cell.Value)
on Error goto 0
if sh is nothing then
Application.ScreenUpdating = False
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
s = ActiveSheet.Name & "!A1"
WS.Hyperlinks.Add Anchor:=cell, Address:="", _
SubAddress:=s, TextToDisplay:=ActiveSheet.Name
ws.activate
Application.ScreenUpdating = True
end if
End If
Next

End Sub

--
Regards,
Tom Ogilvy


"klysell" .(donotspam) wrote in message
...
Hi Tom,

Ok, I've got your code to work - the hyperlinks work great! The only thing
is that if I run the macro again with more names added to the list, the
macro
doesn't initiate (doesn't even indicate an error message). I can't figure
out
why the code isn't cycling through on successive attempts once names are
added....

Thanks in advance (and for Jim and Bob's help as well).
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211



"Tom Ogilvy" wrote:

http://www.j-walk.com/ss/books/index.htm

look at the power programming series for the version of interest.


Stephen Bullen et al
http://www.oaltd.co.uk/

see the book icons/links on the left
--
Regards,
Tom Ogilvy


"klysell" wrote:

Thanks guys!

Hey, do you know what course to take or book to buy to learn VBA
programming
for Excel? I already know how to use Excel tools proficiently but would
like
to focus on expert VBA programming.

Cheers!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211



"Bob Phillips" wrote:

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "C").End(xlUp)
Set Rng = WS.Range("C12", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Set WS = Nothing
On Error Resume Next
Set WS = Worksheets(cell.Value)
On Error GoTo 0
If WS Is Nothing Then
Sheets("Master").Copy
after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
End If
End If
Next
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"klysell" .(donotspam) wrote in message
...
Hi Bob,

I've incorporated your code successfully, but names are being
continuously
added to my list (C12 to C412) from which my worksheet names are
being
generated. I'd like to add an error handling event to your code so
that
the
user can add names once they've run the macro. For example: After
running
the
macro to add 195 names (known at present), I need to add an error
handling
procedure to have VB loop through names until it comes across a
newly
added
name(s) (known in future) at which point it will create a new
worksheet
with
the corresponding name(s) in my "C12:C412" range using the template
in
"Master" without generating an error message. To add to this
problem, I
would
like to add hyperlinks to each of my cells (C12 to C412) so that
the user
can
quickly go to a requested worksheet.

Here is the code that I have thus far:

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "C").End(xlUp)
Set Rng = WS.Range("C12", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Sheets("Master").Copy
after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
End If
Next
End Sub

Your or someone else's assistance would be greatly appreciated! -
TIA
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
H: (613) 907-1211
W: (613) 943-9098

Hi Jim,

Thanks for your reply. I've incorporated some error handling now in the code
but the hyperlinks (inbedded in the macro) only seem to work when the names
are in a one-word format. Any two-word or special character combinations
(such as a comma, etc.), cause the hyperlinks to show a reference error. Do
you know of a way around this?

Thanks in advance,
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211



"Jim Thomlinson" wrote:

This should do it... There are the usual restriction on the sheet name in
terms of the length of the name and you can not name the sheet "History" but
otherwise it should work... You should add validation to the rnage C12:C?? to
ensure valid sheet names...

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "C").End(xlUp)
Set Rng = WS.Range("C12", LastCell)
For Each cell In Rng
If Not SheetExists(cell.Value) And Not IsEmpty(cell) Then
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
cell.Hyperlinks.Add Anchor:=cell, _
Address:="", _
SubAddress:="'" & ActiveSheet.Name & "'!A1", _
TextToDisplay:=cell.Value
End If
Next
End Sub

Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function
--
HTH...

Jim Thomlinson


"klysell" wrote:

Hi Bob,

I've incorporated your code successfully, but names are being continuously
added to my list (C12 to C412) from which my worksheet names are being
generated. I'd like to add an error handling event to your code so that the
user can add names once they've run the macro. For example: After running the
macro to add 195 names (known at present), I need to add an error handling
procedure to have VB loop through names until it comes across a newly added
name(s) (known in future) at which point it will create a new worksheet with
the corresponding name(s) in my "C12:C412" range using the template in
"Master" without generating an error message. To add to this problem, I would
like to add hyperlinks to each of my cells (C12 to C412) so that the user can
quickly go to a requested worksheet.

Here is the code that I have thus far:

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "C").End(xlUp)
Set Rng = WS.Range("C12", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
End If
Next
End Sub

Your or someone else's assistance would be greatly appreciated! - TIA
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
H: (613) 907-1211
W: (613) 943-9098

Hi Bob,

I incorporated your solution and it works well - even with multi-word names
when incorporating the hyperlinking from Jim's code. Thanks very much! You
guys are Excel gurus...
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211



"Bob Phillips" wrote:

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "C").End(xlUp)
Set Rng = WS.Range("C12", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Set WS = Nothing
On Error Resume Next
Set WS = Worksheets(cell.Value)
On Error GoTo 0
If WS Is Nothing Then
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
End If
End If
Next
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"klysell" .(donotspam) wrote in message
...
Hi Bob,

I've incorporated your code successfully, but names are being continuously
added to my list (C12 to C412) from which my worksheet names are being
generated. I'd like to add an error handling event to your code so that
the
user can add names once they've run the macro. For example: After running
the
macro to add 195 names (known at present), I need to add an error handling
procedure to have VB loop through names until it comes across a newly
added
name(s) (known in future) at which point it will create a new worksheet
with
the corresponding name(s) in my "C12:C412" range using the template in
"Master" without generating an error message. To add to this problem, I
would
like to add hyperlinks to each of my cells (C12 to C412) so that the user
can
quickly go to a requested worksheet.

Here is the code that I have thus far:

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "C").End(xlUp)
Set Rng = WS.Range("C12", LastCell)
For Each cell In Rng
If Not IsEmpty(cell) Then
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value
End If
Next
End Sub

Your or someone else's assistance would be greatly appreciated! - TIA
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
H: (613) 907-1211
W: (613) 943-9098