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.
The image below shows a very simple PivotTable containing a few hyperlinks, they look like they don't work if you click on them because they miss the blue underline.
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 executed.
'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) = "http://" OR Left(Target.Value, 8) = "https://" Then
ActiveWorkbook.FollowHyperlink Address:=Target.Value, NewWindow:=True
How to use Event code
Copy VBA code above.
Press Alt + F11 to open the Visual Basic Editor.
Double click on the sheet in project explorer that contains the Pivot Table.
Paste code to sheet module.
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.