• Making Pivot Table Hyperlinks Clickable

    If you’ve ever built a PivotTable that contains hyperlinks, you’ll notice that clicking the hyperlinks doesn’t do anything. This can be a bit frustrating as the reason you put that field on the Pivot in the first place is that it’s valuable information you want to use. When you click the hyperlink, you kind of expect it to open!
    Fortunately, even though hyperlinks don’t work in PivotTables by default, we can make it work with just a tiny bit of VBA code.

    Example

    Let’s assume that you have a PivotTable set up like the following:

    Adding the code

    To add the required code, you’re going to need to know two things:

    1. The name of the file you’re working in. This example is based on a file called “Reconciliation.xlsm”
    2. The worksheet that your PivotTable lives in. In this case, it’s “Summary”

    Now, knowing that, let’s make this work:

    • Press Alt+F11 to open the Visual Basic Editor
    • If the Project Explorer (window 1 in the image below) isn’t showing, press CTRL+R to show it
    • Drill down into your workbook and expand the “Microsoft Excel Objects”
    • Locate the worksheet that holds your PivotTable and double click it

    At this point, it will activate the correct code pane for you to past your code into (marked as 2, above). Simply copy the following code and paste it in there after the last line that start with “Option” (if any exist):

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Target.Cells.Count = 1 Then
            On Error Resume Next
            ActiveWorkbook.FollowHyperlink _
                Address:=CStr(Target.Value), _
                NewWindow:=True
            On Error GoTo 0
        End If
    End Sub
    Finally:

    • Close this window to return to Excel
    • Save the file

    Now you can click on any cell in the worksheet. If it has a valid hyperlink, it will open it for you!

    Caveats

    Using macros blows Excel’s Undo stack. Clicking any cell on this worksheet will cause the Undo actions to clear. It’s nothing serious, just something to be aware of.

    Also, keep in mind that this will only work on this specific worksheet. If you’d like to modify the routine to work on any worksheet, our forums are a good place to ask for help.

     

    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

     

    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

    Comments 2 Comments
    1. jsk685's Avatar
      jsk685 -
      Thanks, the code worked perfectly.
      I appreciate your knowledge!
    1. jbenv2's Avatar
      jbenv2 -
      This was EXTREMELY helpful. My issue is though, I have a report filter and when I show report filter pages to create several tabs, I then have to input the code on to each individual sheet. How do I make it automatic??
  • MVP Logo
  • Recent Forum Posts

    glennm0270

    Excel Refresh All Stopped Working

    Sometime in the last week or so, the Data / Refresh All action stopped working in Excel on one of my Windows machines. I click Data / Refresh All (or...

    glennm0270 Today, 06:56 PM Go to last post
    Madjry

    Keeping excel formating in email body

    The email created does not keep the formatting from Excel.
    I know you can add HTML code to keep the formatting in the email body.

    How...

    Madjry Today, 04:25 PM Go to last post
    ArdusPetus

    extract data from web

    The form is a POST form, not a GET one.
    Your request should be like this:

    Code:
        
    With oXMLH        
        .Open "POST",
    ...

    ArdusPetus Today, 03:41 PM Go to last post
    NormS

    Pivot table

    Your source data is already pivoted. Rearranging it into Month and Values columns will give you what you're looking for....

    NormS 2020-04-04, 11:01 PM Go to last post
    NormS

    If , vlookup and drop down list

    Is this what you are trying to do? I've used INDEX/MATCH to do the equivalent of a VLOOKUP, along with INDIRECT to select the correct column in the price...

    NormS 2020-04-04, 07:04 PM Go to last post