CODEWITHSUNDEEP
excelvbapivot-tableslicers
Joe
Joe

Reputation: 655

Macro button to copy link in pivot table based on Slicer

I'm starting with a Pivot Table that has all my data. One of the data fields is a URL string that I ultimately want to send my users to.

To make searching through the pivot table as easy as possible, I've got a couple slicer windows to let the users click their search criteria. What I'd like to do, is based upon their search criteria (once narrowed down to one result), is have a Macro Button that automatically launches their browser (lets say internet explorer) with the link found in the pivot table.

I can figure out the launching of the browser (or just a copy text), but is there a way that I can program the Macro Button's action based upon the selections of the Slicers that are associated with my pivot table?

Any help would be much appreciated!

Upvotes: 1

Views: 271

Answers (1)

Kresimir L.
Kresimir L.

Reputation: 2441

If I understood you correctly, you want to launch your code when only one link appears in PivotTable (after you apply slicers to it).

You can achieve this with Worksheet_PivotTableChangeSync event and helper cell.

lets assume you have your pivot table in column G, starting from cell G2. Enter this this formula =COUNTA(G3:G20) in cell G1(adjust range to your needs).

enter image description here

then in your worksheet module (not regular module) enter this code.

Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)
Dim a As Long
a = Range("G1").Value
If a = 1 Then
    'your code for link in cell G3
Else
End If
End Sub

Now when you slice your PivotTable to only one selection, COUNTA formula will evaluate to 1 and trigger Worksheet_PivotTableChangeSync event.

Upvotes: 1

Related Questions