Hello,
Ok, I have been playing around with the VBA stuff in excel by what I have researched online to make hyperlinks work in a pivot table but I am having trouble getting this to work.
Here is my situation:
> Source Data has a column with the text "Edit Funding" for each row.
> The "Edit Funding" text is hyperlinked to another worksheet within the workbook.
> The pivot table has several columns of data but one of those columns is the "Edit Funding" column in which I would like for the hyperlink that is active in the source data to be active in the pivot table.
> I have slicers in the pivot table so having the hyperlinks active in the pivot table is essential to the functionality of this workbook.
Example:
COL A COL B COL C COL D
NAME ACCOUNT ACCOUNT BALANCE EDIT FUNDING
John Doe 123456 $35,000 Edit Funding
Janice Cat 234567 $37,000 Edit Funding
So, the Edit Funding Column (Column D in the example above) is in the source data, linked to other worksheets - I would like for this column to be brought into my pivot table with the hyperlinks intact.
Here is what I am using as the code (and I am very new to using VBA so I know this is wrong but I don't know how to fix it):
(And if any better suggestions for a code is available, I would very much appreciate your assistance in creating this!)
---------------
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 with EditFunding
If Left(Target.Value, 8) = "Edit Funding" Then
'Follow hyperlink using cell value as hyperlink address
ActiveWorkbook.FollowHyperlink Address:=Target.Value, NewWindow:=True
End If
End If
Next ptc
End Sub
--------------------------
Thank you for your time!
Ok, I have been playing around with the VBA stuff in excel by what I have researched online to make hyperlinks work in a pivot table but I am having trouble getting this to work.
Here is my situation:
> Source Data has a column with the text "Edit Funding" for each row.
> The "Edit Funding" text is hyperlinked to another worksheet within the workbook.
> The pivot table has several columns of data but one of those columns is the "Edit Funding" column in which I would like for the hyperlink that is active in the source data to be active in the pivot table.
> I have slicers in the pivot table so having the hyperlinks active in the pivot table is essential to the functionality of this workbook.
Example:
COL A COL B COL C COL D
NAME ACCOUNT ACCOUNT BALANCE EDIT FUNDING
John Doe 123456 $35,000 Edit Funding
Janice Cat 234567 $37,000 Edit Funding
So, the Edit Funding Column (Column D in the example above) is in the source data, linked to other worksheets - I would like for this column to be brought into my pivot table with the hyperlinks intact.
Here is what I am using as the code (and I am very new to using VBA so I know this is wrong but I don't know how to fix it):
(And if any better suggestions for a code is available, I would very much appreciate your assistance in creating this!)
---------------
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 with EditFunding
If Left(Target.Value, 8) = "Edit Funding" Then
'Follow hyperlink using cell value as hyperlink address
ActiveWorkbook.FollowHyperlink Address:=Target.Value, NewWindow:=True
End If
End If
Next ptc
End Sub
--------------------------
Thank you for your time!