Use 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:
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.
Hyperlinks category
The macro demonstrated above creates hyperlinks to all worksheets in the current worksheet. You will then be able to quickly […]
The image above shows two hyperlinks, the first hyperlink lets you select a data set automatically based on a dynamic […]
Question: I have a workbook containing a lot of worksheets and data. I need a way to find certain data/sheets […]
Pivot table category
Table of Contents Introduction to pivot tables Create pivot table Group data Analyze data (pivot table) Compare performance, year to […]
Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]
In a previous post:Â How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]
Excel categories
22 Responses to “Use hyperlinks in a pivot table”
Leave a 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.
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)
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 SubGet the Excel *.xlsm file
Hyperlinks-in-pivot-tables-in-a-workbook.xlsm
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
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:
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 SubThanks 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 opened 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 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?.
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 SubYou 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.
[...] 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.
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
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
I have been working on a pivot table for work. When I pasted in all the hyperlinks, one set posted short links which work with the internal security measures. When I change the link to the full address link, it will not allow employees to access the page.
I have 2 columns of links. One is working. One is not. I assume bc it's a short link.
Also if I click on the shortened hyperlink in the original dataset, it works. When I go to the pivot table, it no longer is clickable. Is there a fix for this??