Hi,

After developing a macro to create worksheets based on inputted employee
names contained in C10:C408 on my Summary Sheet (5th sheet), I need to update
links to these constantly changing 200+ spreadsheets (6th sheet and on).
These links are contained on the same row where the user has entered in a new
employee name. Since the name of the new worksheet is not known at time of
development of spreadsheet, the links (e.g. on the 196th row) should refer to
the new spreadsheet (e.g. entered in C196).

Any help would be immensely appreciated!
Thanks in advance,
Kent Lysell

P.S. Here is my code that propagates the names in C10:C408 to their
respective worsheets and creates hyperlinks to each of the newly generated
sheets:

Private Sub CommandButton1_Click()
Dim LastCell As Range, Rng As Range, cell As Range
Dim ws As Worksheet
ActiveSheet.Unprotect Password:="1111"
Set ws = ActiveSheet
Set LastCell = ws.Cells(Rows.Count, "c").End(xlUp)
Set Rng = ws.Range("c10", 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").Visible = True
Sheets("Master").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value

cell.Hyperlinks.Add Anchor:=cell, _
Address:="", _
SubAddress:="'" & ActiveSheet.Name & "'!A1", _
TextToDisplay:=cell.Value
ActiveSheet.Protect Password:="1111"
Sheets("Master").Visible = False
End If
End If
Next
Application.Goto Reference:="Summary"

For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="1111"
Next ws
End Sub


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