Change hyperlink address in all sheets (using Mac 2011)

Question

I have a working Excel VBA code that replace hyperlink text in every sheet. Check the code below

' Only change Hyperlinks in the active sheet
Sub FixHyperlinks()

    Dim wks As Worksheet
    Dim hl As Hyperlink
    Dim sOld As String
    Dim sNew As String

    Set wks = ActiveSheet
    sOld = "%5C"
    sNew = "/"
    For Each hl In wks.Hyperlinks
        hl.Address = Replace(hl.Address, sOld, sNew)
    Next hl

End Sub

The problem is that the workbook have around 30 sheets!!

How can I make this macro run in all the sheets in the active workbook ?

Answer 1

use a for each loop to loop over all sheets in ThisWorkbook.Worksheets:

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    'do something
Next ws

so in your case:

For Each ws In ThisWorkbook.Worksheets

    For Each hl In ws.Hyperlinks

        hl.Address = Replace(hl.Address, sOld, sNew)
    Next hl
Next ws

Answer 2

The only thing you need to add is to loop the Workbook for all Sheets, see in modifed code below:

Sub FixHyperlinks()

Dim wb As Workbook
Dim wks As Worksheet
Dim hl As Hyperlink
Dim sOld As String
Dim sNew As String

Set wb = ThisWorkbook

' loop ThisWorkbook all Sheets
For Each wks In wb.Worksheets
    sOld = "%5C"
    sNew = "/"
    For Each hl In wks.Hyperlinks
        hl.Address = Replace(hl.Address, sOld, sNew)
    Next hl
Next wks

End Sub