How can I edit a hyperlink in more than one cell at once

[Boysie]

I have a workbook with appoximately 130 worksheets in it. On each sheet one
cell is linked to the "master" sheet which lists all of the others. This
sheet is named "Words". I now need another sheet named "Words" and the
"Words" sheet changed to "Main". Easy at first but all the other sheets are
linked to the "Words" sheet.

Is it possible to edit the same cell in all of the worksheets at the same?

Thanks for any help

 

[paul]

i dont know about 130 sheets but if you hold down ctrl while clicking sheet
tbs you select ALL those sheets and when you edit a particular cell the same
cell on each sheet is changed

 

[Boysie]

Paul

Thanks for the info but when I tried it the "Edit Hyperlink" option is
greyed out.

It may help if I add it is the same cell on each of the worksheets.

Boysie

 

[Dave Peterson]

You could use something like this to remove the hyperlinks:

Option Explicit
Sub testme()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
With wks.Range("a1")
If .Hyperlinks.Count > 0 Then
.Hyperlinks(1).Delete
End If
End With
Next wks
End Sub

Then instead of using insert|hyperlink to create the hyperlink, you could use
the =hyperlink() worksheet formula:

Group all your sheets that get the hyperlink. (Select the first, and
ctrl-click, or shift-ctrl-click to extend the selection.)

Then select the cell that gets the hyperlink and type this:
=HYPERLINK("#"&CELL("address",'Words'!A1),"click me")

Then rightclick on any of the grouped worksheet tabs and select ungroup sheets.

If you change the name of Words, this =hyperlink() worksheet function will
adjust -- just like any other formula.


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Boysie]

Thanks Dave. Much appreciated. I'll have to digest this and work on it.

Regards

Boysie