PDA

View Full Version : Solved: Setting Pivot Table Filter with VBA



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

magelan
03-11-2013, 02:31 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

I think the only way to catch if the field doesnt exist is to check for that first

edit:

dont forget to run a "clear all filters" on pivot tables when you are done, or workbook corruption happens.

JimS
03-12-2013, 01:22 PM
Ok, thanks.

I'll just check for a valid value before setting the Filter.

Thought I'd ask first.

JimS

snb
03-12-2013, 03:27 PM
Private Sub M_snb()
for each it in ActiveSheet.PivotTables("PT5").PivotFields("Submitters Name").pivotitems
c01 = c01 & "," & it.Name
Next
msgbox mid(c01,2)
End Sub