View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Dave Peterson Dave Peterson is offline
external usenet poster
 
Posts: 35,218
Default Managing links to Summary from constantly changing worksheet n

No thanks to sending me the workbook--I wouldn't open it anyway.

But if you have other hyperlinks in the same row that need to be adjusted, you
can look through those cells for hyperlinks--and just delete those hyperlinks
and add them back the way you want.

But it's difficult to guess what you really mean.

====
A nice thing about using the newsgroups is that there are lots of people who can
help.

klysell wrote:

Hi Dave,

Yes, the hyperlinks do work well. But there are still references in the same
row as where the user entered the new employee name on the Summary sheet that
capture date from specific cells in the newly created worksheet. I can e-mail
you the spreadsheet if you wish... I'm amazed at you guys for helping out the
Excel global community! I'd buy you guys a beer if you were in Ottawa :-)
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
(613) 907-1211

"Dave Peterson" wrote:

Just looking at your code...

Doesn't this line do what you want?

cell.Hyperlinks.Add Anchor:=cell, _
Address:="", _
SubAddress:="'" & ActiveSheet.Name & "'!A1", _
TextToDisplay:=cell.Value

It adds the hyperlink to the cell in column C and points to the new worksheet
(cell A1)?

What am I missing?


klysell wrote:

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


--

Dave Peterson


--

Dave Peterson