How to update all worksheets, not just the activesheet? 1

[childrenfirst]

Hi,

I have a module in Excel, which will update the hyperlinks in column AD when a user downloads the spreadsheet and open it from his computer. The problem is that I want the macro to update the AD columns in all worksheets in the workbook, not just the activesheet. How should I modify the code?

Many thanks,
childrenfirst
-----------------------------------------
The current code:

Private Sub Workbook_Open()

RowCount = Cells(Cells.Rows.Count, "AD").End(xlUp).Row
For i = 2 To RowCount

Range("AD" & i).Select

original = ActiveCell.Value
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=ActiveCell.Value

Next
End Sub

 

[PHV]

Typed, untested:
Private Sub Workbook_Open()
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
RowCount = sh.Cells(sh.Cells.Rows.Count, "AD").End(xlUp).Row
For i = 2 To RowCount
sh.Range("AD" & i).Select
original = ActiveCell.Value
sh.Hyperlinks.Add Anchor:=Selection, Address:=ActiveCell.Value
Next
Next
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[childrenfirst]

Hello PHV,

It worked very well!! Thank you so much for your help

Here comes the star

 

[duGly]

I like PHV's solution. Here is a variation (untested code) on his theme:

Private Sub Workbook_Open()
    Dim mySheet As Worksheet
    Dim myRange As Range
    Dim i%
    
    For Each mySheet In ActiveWorkbook.Worksheets
        For i = 2 To mySheet.Cells(mySheet.Cells.Count, "AD").End(xlUp).Row
            Set myRange = mySheet.Range("AD" & i)
            mySheet.Hyperlinks.Add Anchor:=myRange, Address:=myRange.Value
        Next    ' i
    Next    ' mySheet

End Sub

The biggest difference with this version is that it never involves selecting or activating worksheets and/or cells. VBA code in Excel runs tons faster if you can avoid selecting, even with ScreenUpdating disabled. Give this a try and see if it works for you.