vba On error Hide Pivot Table, Resume Next (unhide if no error)

[Melimob]

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.

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

 

[Melimob]

hey all .. just to let you know I managed to make it work with this code.

Option Explicit

Sub ChangePiv()
 
'On Error Resume Next
   On Error GoTo ErrorHandler
  
    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



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

PT1.TableRange2.EntireRow.Hidden = False
ActiveSheet.PivotTables(1).PivotCache.Refresh
With PT1
    
  PF1.CurrentPageName = "[LeadData].[Introducer (Actual)].&[" & Choice1 & "]"
  PF2.CurrentPageName = "[LeadData].[Lead Month].&[" & Choice2 & "]"
  PF3.CurrentPageName = "[LeadData].[Lead Year].&[" & Choice3 & "]"
 
End With
    Exit Sub
ErrorHandler:
  
      
   PT1.TableRange2.EntireRow.Hidden = True

End Sub