Hi Bob, Tom, and Jim:
When I incorporated the macro which disseminated the list of names on
"Summary" sheet in C9:C408 to worksheets, I came across another problem: the
links corresponding to the worksheet names in C9:408. Each worksheet that is
generated by the maco has cells that link back to the Summary sheet adjacent
to the employee name (in C9:C408). For example, there would be salary data in
E41 of that macro-generated worksheet (EmployeeName1) that would have to link
back to the Summary sheet in the same row as EmployeeName1. By design, all
newly inputted employee names in Column C on Summary sheet, that would be
propagated to new worksheets via the macro, would have the proper links from
this new worksheet back to the Summary Sheet in the same row as the inputted
employee name.
Here is my macro thus far (which ideally would establish the appropriate
cell link back to the Summary sheet):
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
ActiveSheet.Protect Password:="1111"
cell.Hyperlinks.Add Anchor:=cell, _
Address:="", _
SubAddress:="'" & ActiveSheet.Name & "'!A1", _
TextToDisplay:=cell.Value
Sheets("Master").Visible = False
End If
End If
Next
Application.Goto Reference:="Summary"
ActiveSheet.Protect Password:="1111"
End Sub
Any help would be greatly appreciated!
Thanks in advance,
Kent Lysell
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211
|
Similar Threads
