## Follow hyperlinks in a pivot table

*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:**

Here is a simple pivottable, in sheet1.

**Vba code**

- Copy vba code below
- Press Alt + F11
- Double click your sheet in project explorer

- Paste into code window
- Return to Microsoft Excel

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) = "http://" Then ActiveWorkbook.FollowHyperlink Address:=Target.Value, NewWindow:=True End If End If Next ptc End If End Sub

Now try clicking a cell containing a hyperlink in the pivot table!

**Download excel 2007 workbook *.xlsm**

### Category: Hyperlinks

Create links to all sheets in a workbook

The macro demonstrated below creates hyperlinks to all sheets in current workbook. Select a start cell and then run macro […]Comments(30) Filed in category: Excel, Hyperlinks

Locate lookup values in a table [HYPERLINK]

Today I´ll show you how to search a table column and jump to that table cell using the hyperlink function. When […]Comments(14) Filed in category: Excel, Hyperlinks, Vlookup

Quickly jump to last row in a data set using excel hyperlink function

Today I´ll show you how to create a useful hyperlink in excel. If you click the link it will take […]Comments(5) Filed in category: Excel, Hyperlinks

Quickly create links to sheets, tables, pivot tables and named ranges in a workbook

A week ago I posted Create links to all sheets in a workbook and today I want to show you how […]Comments(3) Filed in category: Excel, Hyperlinks, Named range

List all hyperlinks in worksheet

The macro in this blog post lists all hyperlinks in a worksheet. It also lists all words beginning with "http" […]Comments(3) Filed in category: Excel, Hyperlinks, Worksheet

Quickly select a data set or an excel defined table [HYPERLINK]

If you often copy data sets or tables, the following technique might be interesting! The animated gif shows two hyperlinks, […]Comments(2) Filed in category: Excel, Hyperlinks

Excel: Add screentip to your hyperlink

Adding a screentip for your hyperlink can be a useful tool to inform other workbook users about the link. Instructions:Comments(1) Filed in category: Excel, Hyperlinks

How to navigate quickly in a complex excel workbook using hyperlinks

Question: I have a huge workbook. I need a way to find certain data/sheets quickly. How can i do this? […]Comments(1) Filed in category: Excel, Hyperlinks

Insert hyperlinks to all files in current folder

The macro creates a new sheet. Inserts all filenames in current folder as hyperlinks except the current workbook. VBA Code […]Comments(1) Filed in category: Excel, Files and folders, Hyperlinks

### Category: Pivot table

Comments(92) Filed in category: Excel, Pivot table

Change pivot table data source using a drop down list

In this excel 2007 tutorial I am going to show you how to quickly change pivot table data source using […]Comments(18) Filed in category: Combobox, Drop down lists, Excel, Pivot table

How to create a dynamic pivot table and refresh automatically in excel

David Hager commented: Looks like an easy pivot table solution to me. Thanks for your comment! Now i know how to […]Comments(17) Filed in category: Excel, Pivot table

Comments(12) Filed in category: Excel, Normalize data, Pivot table, Rearrange values

Excel 2007 pivot table: Count unique distinct records (rows)

Table of Contents Count unique distinct records Count duplicate records Excel 2007 pivot table: Count unique distinct records (rows) The […]Comments(9) Filed in category: Excel, Pivot table, Unique distinct records

Comments(7) Filed in category: Excel, Pivot table, Related tables

Google spreadsheets: Pivot tables

This week Google Docs introduced Pivot Tables in Google Spreadsheets. I am not that familiar to google spreadsheets but I had to […]Comments(4) Filed in category: Excel, Pivot table

Sum security holdings monthly and yearly in a pivot table

Did you know that you can use a pivot table to summarize portfolio holdings at any point in time? If you trade […]Comments(4) Filed in category: Excel, Finance, Pivot table

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 […]Comments(4) Filed in category: Count values, Excel, Pivot table, Unique distinct values

### 19 Responses to “Follow hyperlinks in a pivot table”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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

Sean,

Thanks!

I have added comments to the code.

This code works for all pivotTables in a workbook.

Copy/paste the code below to "ThisWorkbook" code module in project explorer (Alt+F11)

Download excel *.xlsm fileHyperlinks-in-pivot-tables-in-a-workbook.xlsm

The follow code from above works well except that the first time I click on a hyperlink, I get a Run Time Error 13. When I reset and click 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

Brian,

Run Time Error 13 - Type mismatch

Which line is the problem?

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

Sorry, Oscar of course :)

Christian,

You are right. If you select more than one cell you get an error.

Try this:

Thanks for commenting!

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?

The Attachement has no Excel files :(

Code is not working with xl 2007

deepak ji paulson,

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

Oscar, you are awesome! millions of thanks

HI,

THe link is working fine but if i need to click on the Text or value column and if the hyperlink has to be opened what could be the solution for that?.

Harshitha,

You probably need to change this line:

If Left(ptc.TableRange1.Cells(Target.Row - ptc.TableRange1.Row + 1,

1).Value, 7) = "https://" ThenBolded value is the column number for the links in the Pivot table range.

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

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

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

Made my day. Works like a charm

Nav,

thanks.