JimS
03-11-2013, 02:08 PM
I'm trying to set a Pivot Table filter using the code below.
This works but it "hard" sets the filter even if the value is not in the pivot table source data.
Is there a way to detect or catch that the value is not a valid choice?
Thanks...
JimS
Sub Pivot_Filter2()
Dim s As String
Dim R As Long
Dim C As Integer
R = ActiveCell.Row
On Error GoTo ErrHandler:
s = Cells(R, 3).Value
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Sheet5").Visible = True
Sheets("Sheet5").Select
ActiveSheet.PivotTables("PT5").PivotFields("Submitters Name").CurrentPage = s
Exit Sub
ErrHandler:
msgbox "No records were found"
End Sub
This works but it "hard" sets the filter even if the value is not in the pivot table source data.
Is there a way to detect or catch that the value is not a valid choice?
Thanks...
JimS
Sub Pivot_Filter2()
Dim s As String
Dim R As Long
Dim C As Integer
R = ActiveCell.Row
On Error GoTo ErrHandler:
s = Cells(R, 3).Value
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Sheet5").Visible = True
Sheets("Sheet5").Select
ActiveSheet.PivotTables("PT5").PivotFields("Submitters Name").CurrentPage = s
Exit Sub
ErrHandler:
msgbox "No records were found"
End Sub