prometeusz
12-22-2017, 02:25 AM
Hi
I know it is possible to do: see the first file attached. As you click on the pivot table cells, you get directed to the relevant tabs.
Have tried to achieve this in my file, but for some reason, the same trick does not appear to work - see "zero test 2" file.
The vba code involved is as follows:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim pvtTable As PivotTable
Dim shtTemp As Worksheet
If Target.Cells.Count = 1 Then
For Each pvtTable In Target.Parent.PivotTables
If Not Intersect(pvtTable.RowRange, Target) Is Nothing Then
On Error Resume Next
Set shtTemp = Worksheets(Trim(Target.Value))
On Error GoTo 0
If Not shtTemp Is Nothing Then
Application.Goto shtTemp.Range("A1")
End If
End If
Next
End If
End Sub
What difference makes it impossible in zero test 2 file to achieve the same result? It is not because the source and the pivot table are separate. I would hope to complete the task by Christmas, but anything later than that will still be great because the file will be developed on a continuous basis.
Many thanks
T
I know it is possible to do: see the first file attached. As you click on the pivot table cells, you get directed to the relevant tabs.
Have tried to achieve this in my file, but for some reason, the same trick does not appear to work - see "zero test 2" file.
The vba code involved is as follows:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim pvtTable As PivotTable
Dim shtTemp As Worksheet
If Target.Cells.Count = 1 Then
For Each pvtTable In Target.Parent.PivotTables
If Not Intersect(pvtTable.RowRange, Target) Is Nothing Then
On Error Resume Next
Set shtTemp = Worksheets(Trim(Target.Value))
On Error GoTo 0
If Not shtTemp Is Nothing Then
Application.Goto shtTemp.Range("A1")
End If
End If
Next
End If
End Sub
What difference makes it impossible in zero test 2 file to achieve the same result? It is not because the source and the pivot table are separate. I would hope to complete the task by Christmas, but anything later than that will still be great because the file will be developed on a continuous basis.
Many thanks
T