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