Author: Oscar Cronquist Article last updated on February 03, 2019

Sean asks:

Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise the cell is a hyperlink. In addition, when I update the indivital pivot tables, it looks at the data in the "Notes" field and recognise it as a hyperlink.

Answer:

The image below shows a very simple PivotTable containing a few hyperlinks, they look like they don't work if you press with left mouse button on them because they miss the blue underline.

Vba code

The following macro is an Event macro meaning it must be located in a sheet module. It runs when a selection changes on a worksheet. For example, every time you select a cell on sheet1 the macro is rund.

'The Event name must be Worksheet_SelectionChange(ByVal Target As Range), you can't change this.
Sub Worksheet_SelectionChange(ByVal Target As Range)

'Declare variables and data types
Dim ptc As PivotTable, Value As Variant, Rng As Range

'Check if only 1 cell is selected
If Target.Cells.Count = 1 Then

    'Iterate through each pivot table in active worksheet
    For Each ptc In ActiveSheet.PivotTables
        
        'If selected cell intersects with pivot table cell range
        If Not Intersect(Target, Range(ptc.TableRange1.Address)) Is Nothing Then

            'Does the cell contain a http or https link?
            If Left(Target.Value, 7) = "https://" OR Left(Target.Value, 8) = "https://" Then

                'Open hyperlink
                ActiveWorkbook.FollowHyperlink Address:=Target.Value, NewWindow:=True
            End If
        End If
    Next ptc
End If

End Sub

How to use Event code

  1. Copy VBA code above.
  2. Press Alt + F11 to open the Visual Basic Editor.
  3. Double press with left mouse button on the sheet in project explorer that contains the Pivot Table.
  4. Paste code to sheet  module.
  5. Exit VBE and return to Microsoft Excel.
Note, save your workbook with file extension *.xlsm (macro-enabled workbbook) in order to keep the macro in your workbook.

Get the Excel file


Hyperlinks-in-pivot-tables.xlsm