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