Dynamic Hyperlink Loop All Sheets

Vbalearner85

Board Regular
Joined
Jun 9, 2019
Messages
131
Office Version
  1. 2016
Platform
  1. Windows
Hi ..I have below the macro, which created dynamic hyperlinks via looping in all sheets of the workbook(some 150 tabs)..

Column BN is where hyperlinks are created; based upon the condition that time >equivalent cell in column BA (else blank the target cell)
Column B is source data where hyperlinks of column BN should refer to.

It is working fine but is too slow...Any optimization edits are welcome. Also, is it possible to not use formulas in cells and still create hyperlinks.(as multiple formulas are making files slow)?

Regards,
VbaLearner85


VBA Code:
Sub CLL()
    Dim xSh As Worksheet
    Application.ScreenUpdating = False
    For Each xSh In Worksheets
        xSh.Select
        Call RunCode
    Next
    Application.ScreenUpdating = True
End Sub
Sub RunCode()
    'Creating Hyperlinks
  
Dim LastRow As Long
Dim rng As Range, C As Range
Range("BA2").Select     'Column BA has source conditional data  for which hyperlink is needed--refer below
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("BN2").Select    'Copying Column BA data to column BN..to use for a condition for inputting hyperlink Formula in each cell
ActiveSheet.Paste
Range("BN1").Select

With ActiveSheet
    LastRow = .Cells(.Rows.Count, "BN").End(xlUp).Row
    Set rng = .Range("BN2:BN" & LastRow)
    For Each C In rng
        If C.Value2 > ActiveSheet.Range("$BZ$1").Value2 Then      'creating hyperlinks via formula in col BN if cell value is > Range("$BZ$1")..which is already input as "00:00:30"
           C.FormulaR1C1 = "=HYPERLINK(""#""&CELL(""address"",INDEX(r2c2:r25000c2,MATCH(rc52,r2c2:r25000c2,0))),TEXT(rc52,""hh:mm:ss""))"
        ElseIf C.Value2 < Sheets("Index").Range("$BZ$1").Value2 Then
            C.Value = " "
        End If
    Next C
End With
ActiveSheet.Calculate

End Sub
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Forum statistics

Threads
1,147,474
Messages
5,741,342
Members
423,656
Latest member
Medrok2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top