Use hyperlinks in a pivot table

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

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

Copy VBA code above.
Press Alt + F11 to open the Visual Basic Editor.
Double press with left mouse button 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.

Get the Excel file

Hyperlinks-in-pivot-tables.xlsm

Create links to all sheets in a workbook programmatically

The macro demonstrated above creates hyperlinks to all worksheets in the current worksheet. You will then be able to quickly […]

Locate lookup values in an Excel table [HYPERLINK]

Today I'll show you a formula that returns a hyperlink pointing to a location based on a lookup value. When […]

Navigate to first empty cell using a hyperlink formula

This article will demonstrate how to create a hyperlink that takes you to the first empty cell below data in […]

List all hyperlinks in worksheet programmatically

The macro in this blog post lists all hyperlinks in a worksheet. It also lists all words beginning with "http" […]

Macro creates links to all sheets, tables, pivot tables and named ranges

This article demonstrates a macro that automatically populates a worksheet with a Table of Contents, it contains hyperlinks to worksheets, […]

List files in folder and create hyperlinks (VBA)

This article demonstrates a macro that populates a new worksheet with filenames from the active folder which is the same […]

Easily select data using hyperlinks

The image above shows two hyperlinks, the first hyperlink lets you select a data set automatically based on a dynamic […]

How to navigate quickly in a complex workbook using hyperlinks

Question: I have a workbook containing a lot of worksheets and data. I need a way to find certain data/sheets […]

Auto refresh a pivot table

In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]

Change PivotTable data source using a drop-down list

In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]

How to create a dynamic pivot table and refresh automatically

This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete […]

Normalize data [VBA]

To be able to use a Pivot Table the source data you have must be arranged in way that a […]

Count unique distinct records (rows) in a Pivot Table

Excel 2013 allows you to count unique distinct values in a pivot table, this article explains how to use a […]

Pivot Table calendar

This article demonstrates how to build a calendar in Excel. The calendar is created as a Pivot Table which makes […]

Count unique distinct values in an Excel Pivot Table

ExcelBeginner asks: I have a small problem that I am not sure on how to solve. I now have a […]

How to calculate totals of stock transactions based on dates

Did you know that you can use a pivot table to summarize portfolio holdings at any point in time? If you trade […]

Disable autofit column widths for Pivot table

I read this interesting article Quick Trick: Resizing column widths in pivot tables on the Microsoft Excel blog. It is […]

Comments (21)

21 Responses to “Use hyperlinks in a pivot table”

Sean says:

September 30, 2011 at 2:26 pm

Oscar,

That seems to work a treat. because I have 3 pivot tables on individual worksheets that are not the same worksheet as the pivot data, I have placed the code on each of the pivot table sheets. I'm assuming this is correct, as it didn't work on the other pivot table sheets where I hadn't included the code.

Any chance to could add comments to the code, so that I can work out what the subroutine is doing ?

The help is appreciated and hopefully it will help others as well.

Kind Regards
Sean

Reply
Oscar says:

October 11, 2011 at 9:19 pm

Sean,

Thanks!

I have added comments to the code.

Reply
Oscar says:

October 12, 2011 at 1:00 pm

This code works for all pivotTables in a workbook.

Copy/paste the code below to "ThisWorkbook" code module in project explorer (Alt+F11)
```
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Dim ptc As PivotTable, Value As Variant, Rng As Range

For Each ptc In Worksheets(Sh.Name).PivotTables
    If Not Intersect(Target, Range(ptc.TableRange1.Address)) Is Nothing Then
        If Left(Target.Value, 7) = "https://" Then
            ActiveWorkbook.FollowHyperlink Address:=Target.Value, NewWindow:=True
        End If
    End If
Next ptc

End Sub
```
Get the Excel *.xlsm file
Hyperlinks-in-pivot-tables-in-a-workbook.xlsm

Reply
- Brian says:
  
  April 5, 2013 at 3:54 pm
  
  The follow code from above works well except that the first time I press with left mouse button on a hyperlink, I get a Run Time Error 13. When I reset and press with left mouse button again, it's fine. What could be the cause of this. Code is below:
  
  Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim ptc As PivotTable
  'Find every pivotTable in ActiveSheet
  For Each ptc In ActiveSheet.PivotTables
  'Check if selected cell is a pivot table cell
  If Not Intersect(Target, Range(ptc.TableRange1.Address)) Is Nothing Then
  'Check if the cell value begins wih https://
  If Left(Target.Value, 7) = "https://" Then
  'Follow hyperlink using cell value as hyperlink address
  ActiveWorkbook.FollowHyperlink Address:=Target.Value, NewWindow:=True
  End If
  End If
  Next ptc
  End Sub
- Oscar says:
  
  April 8, 2013 at 8:26 am
  
  Brian,
  
  Run Time Error 13 - Type mismatch
  
  Which line is the problem?
- Christian says:
  
  April 26, 2013 at 3:23 pm
  
  Hey Brian,
  
  I get the same Error 13, when I select a range of cells.
  Following Line is (according to Excel) the problem:
  If Left(Target.Value, 7) = "https://" Then
  
  Best,
  Christian
- Christian says:
  
  April 26, 2013 at 3:28 pm
  
  Sorry, Oscar of course :)
- Oscar says:
  
  April 29, 2013 at 9:48 am
  
  Christian,
  
  You are right. If you select more than one cell you get an error.
  
  Try this:
```
Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ptc As PivotTable, Value As Variant, Rng As Range
If Target.Cells.Count = 1 Then
    For Each ptc In ActiveSheet.PivotTables
        If Not Intersect(Target, Range(ptc.TableRange1.Address)) Is Nothing Then
            If Left(Target.Value, 7) = "https://" Then
                ActiveWorkbook.FollowHyperlink Address:=Target.Value, NewWindow:=True
            End If
        End If
    Next ptc
End If

End Sub
```
  Thanks for commenting!
- Nicky PIke says:
  
  June 12, 2014 at 9:14 pm
  
  Oscar,
  
  The original code works well for me, however, I also get the mismatch error in selecting multiple cells. I tried the new code that you posted to fix this error, but when using it, the follow hyperlink no longer works. Any ideas?
deepak ji paulson says:

October 26, 2011 at 2:18 pm

The Attachement has no Excel files :(

Code is not working with xl 2007

Reply
Oscar says:

October 26, 2011 at 3:14 pm

deepak ji paulson,

I opened both files and they work here. I am using excel 2007.

Reply
palie says:

June 19, 2012 at 9:48 am

Oscar, you are awesome! millions of thanks

Reply
- Harshitha says:
  
  October 30, 2012 at 9:17 am
  
  HI,
  
  THe link is working fine but if i need to press with left mouse button on the Text or value column and if the hyperlink has to be opened what could be the solution for that?.
- Oscar says:
  
  October 31, 2012 at 2:47 pm
  
  Harshitha,
```
Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ptc As PivotTable, Value As Variant, Rng As Range

For Each ptc In ActiveSheet.PivotTables
    If Not Intersect(Target, Range(ptc.TableRange1.Address)) Is Nothing Then
        If Left(ptc.TableRange1.Cells(Target.Row - ptc.TableRange1.Row + 1, 1).Value, 7) = "https://" Then
            ActiveWorkbook.FollowHyperlink Address:=ptc.TableRange1.Cells(Target.Row - ptc.TableRange1.Row + 1, 1).Value, NewWindow:=True
        End If
    End If
Next ptc

End Sub
```
  You probably need to change this line:
  
  If Left(ptc.TableRange1.Cells(Target.Row - ptc.TableRange1.Row + 1, 1).Value, 7) = "https://" Then
  
  Bolded value is the column number for the links in the Pivot table range.
Hyperlinks in Excel Pivots? says:

January 6, 2013 at 3:55 pm

[...] Use a VBA solution, as described here: Follow hyperlinks in a pivot table | Get Digital Help - Microsoft Excel resource [...]

Reply
Paul says:

June 18, 2013 at 3:51 pm

I used this simple code. I cannot follow the hyperink i the expanded pivot table. However, the hyperlink i working on the cell that it was created in BEFORE the pivot was collapsed. Any ideas anyone?

Range("L7").Select
Selection.End(xlDown).Select
irow = ActiveCell.Row
Range("e6:e" & irow).Select
For Each Cell In Selection
Cell.Select
If Cell.Value "" Then
If Left(Cell.Value, 7) = "https://" Then
URL = Cell.Value
Else
URL = "https://" + Cell.Value
End If
ActiveSheet.Hyperlinks.Add anchor:=Cell, _
Address:=URL, TextToDisplay:=Cell.Value
End If
Next

Reply
Scott says:

September 9, 2014 at 3:35 pm

This was very helpful to me. Thanks for the post.

Reply
Nav says:

February 19, 2017 at 10:41 pm

Made my day. Works like a charm

Reply
- Oscar says:
  
  February 20, 2017 at 3:25 pm
  
  Nav,
  
  thanks.
Leanor says:

June 24, 2020 at 9:05 am

Hello,

I have a question about the hyper-link code.
If I understand correctly, the code you wrote works for hyper-links to websites.
What if i have in my worksheet hyper-links to files on my computer?

Thank you,
Leanor

Reply
Ester mor perez says:

July 27, 2021 at 9:00 pm

Hii, thank u very much for all ur help here,
I did the macro u wrote above here,
My links are pic and because I cannot put pic in pivot I use the link with ur macro, Is there a possibility that moving with the mouse the images will appear? Thank u

Reply

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form