Hi All, have this code but instead of 'On Error Resume Next' I want to hide the pivot table and then upon next choice without an error, unhide.
At the moment, the pivot table just shows data for the previous client selected upon error as I have the 'on error resume next'.
I tried to add the code I thought would achieve this in place of 'on error resume next' (see commented out lines) but this doesn't seem to work?
Be grateful for any advice.
Many thanks,
Melissa
At the moment, the pivot table just shows data for the previous client selected upon error as I have the 'on error resume next'.
I tried to add the code I thought would achieve this in place of 'on error resume next' (see commented out lines) but this doesn't seem to work?
Be grateful for any advice.
VBA Code:
Option Explicit
Sub ChangePiv()
On Error Resume Next
Dim PT1 As PivotTable
Dim PF1 As PivotField
Dim PF2 As PivotField
Dim PF3 As PivotField
Dim Choice1 As String
Dim Choice2 As String
Dim Choice3 As String
'On Error GoTo ErrorHandler
' Exit Sub
'ErrorHandler:
' PT1.PivotTables("PivotTable5").EntireRow.Hidden = True
' Resume Next
Set PT1 = Worksheets("INTRODUCER DASHBOARD").PivotTables("PivotTable5")
Set PF1 = PT1.PivotFields("[LeadData].[Introducer (Actual)].[Introducer (Actual)]")
Set PF2 = PT1.PivotFields("[LeadData].[Lead Month].[Lead Month]")
Set PF3 = PT1.PivotFields("[LeadData].[Lead Year].[Lead Year]")
Choice1 = Worksheets("INTRODUCER DASHBOARD").Range("R1").Value
Choice2 = Worksheets("INTRODUCER DASHBOARD").Range("C3").Value
Choice3 = Worksheets("INTRODUCER DASHBOARD").Range("C4").Value
With PT1
PF1.CurrentPageName = "[LeadData].[Introducer (Actual)].&[" & Choice1 & "]"
PF2.CurrentPageName = "[LeadData].[Lead Month].&[" & Choice2 & "]"
PF3.CurrentPageName = "[LeadData].[Lead Year].&[" & Choice3 & "]"
End With
End Sub
Many thanks,
Melissa