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