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

Melimob

Active Member
Joined
Oct 16, 2011
Messages
396
Office Version
  1. 365
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.



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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
hey all .. just to let you know I managed to make it work with this code.

VBA 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
 
Upvote 0
Solution

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top