Repairing multiple broken hyperlinks in Excel Spreadsheet

[Guest]

I have limited knowledge of Excel macro writing etc. but have inherited an
Excel spreadsheeet with many hyperlinks to other worksheets on a server. They
are performance management sheets for around 35 staff x 12 (months) plus
quarterly, six monthly and annual) worksheets. Individual results aggregated
to measure team performance etc. Everything working fine, but server is being
relocated shortly and links will break.

No problem to re-build the links using Find and Replace, but every worksheet
is protected so has to be manually unprotected to allow this to happen (Don't
they??). Can anyone please suggest a quicker way of opening and later
closing each worksheet to allow Find and Replace to work? I do not have
access to the person who wrote the original spreadsheets but I do have the
passwords etc. Many thanks.

 

[Gord Dibben]

silver

Do all the sheets have the same password or are they all unique?

For the former..........

Sub UnprotectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Unprotect Password:="justme"
Next n
Application.ScreenUpdating = True
End Sub

If unique password for each sheet, I would suggest you have to unprotect each
one using the unique password.


Gord Dibben Excel MVP