That's right, except there's only one "n", #tabSkany
Hi again docmchem,
Try the following macro and see if it does what you want. The fact you didn't know what "if VBA is an option" meant I am going to go through this is some detail to make sure you know what you are doing (my apologies if this is actually overkill).
1. Make a COPY of your workbook to test this out on.
2. In the COPY of your workbook, press Alt+F11 to open the VBA Developer window.
3. In the VBA Developer window, find the name of your workbook from the project pane running down the left hand side of the screen.
4. Once you have found it, right-click on ThisWorkbook and select
Insert->Module
5. In the new window that pops up, copy and paste in the following code:
Rich (BB code):
Public Sub Update_Hyperlinks()
' Defines Cell as Range
Dim Cell As Range
' For each cell in selction
For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
' If the cell is not blank then...
If Cell <> "" Then
' Add #tabSkany to the end of the cell value
Cell.Value = Cell.Value & "#tabSkany"
' Recreate hyperlink based on the new cell value
ActiveSheet.Hyperlinks.Add Cell, Cell.Value
End If
Next
End Sub
6. Go back to the COPY of your workbook, go to the Developer tab at the top of the screen and click on the
Insert->Button (Form Control) button (the first option in the Insert menu)
7. The mouse cursor will change from an arrow to a cross and will allow you to "draw" out a button (just draw a square or rectangle and let go of the mouse button)
8. In the new window that pops up, double-click on Update_Hyperlinks to attach the macro to your new button. The new window will close.
9. Ctrl+Click on the first cell containing your hyperlinks to select the cell.
10. Scroll down your page to the bottom of the data (where the hyperlinks stop) then Shift+Click on the last hyperlink. This should have selected all cells containing your hyperlinks.
11. Scroll back up to your macro button and press it.
If more hyperlinks are added in the future, simply select all of the cells that haven't already been updated and click the button again to add the #tabSkany suffix.
I hope this helps and hasn't confused you too much. If I have gone into too much detail as you already knew most of this, I apologise.