Macro Update Pivot Table

[rjacmuto32]

I have this code that updates a pivot table filter based on a manager name. My problem is when the cell value in C3 = All, is there is no manager name called All. How can I add a line of code to clear the filter. I know how to write the clear filter but I don't know how to this. When C3 = All


Dim xCriteria As String


Sheets("Pivot_Projects").Select

ActiveSheet.PivotTables("PivotTable2").PivotFields("Last Name"). _

ClearAllFilters

xCriteria = Range("C3").Value

ActiveSheet.PivotTables("PivotTable2").PivotFields("Last Name").PivotFilters _

.Add Type:=xlCaptionEquals, Value1:=xCriteria


How do I add in something at the end, that if C3 = All to run just the clear filter line?

 

[Luke M]

[pre]

Sheets("Pivot_Projects").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("Last Name"). _
ClearAllFilters
xCriteria = Range("C3").Value
If UCase(xCriteria) <> "ALL" Then
ActiveSheet.PivotTables("PivotTable2").PivotFields("Last Name").PivotFilters _
.Add Type:=xlCaptionEquals, Value1:=xCriteria
End If

[/pre]

 

[rjacmuto32]

Thanks. You gave me the piece about the xCriteria too.

 

[Luke M]

No problem...and I thought it looked familiar. =)

 

[rjacmuto32]

I did this one a while back and I'm expanding on it and i'm stuck again.


I have a question on a VB Code to Filter a Pivot Table. I"m stuck with 1 part of the code that doesn't work.


I have a Pivot Table (PivotTable2) in my sheet.

in the Report Filter Area I have the name P&L Start Date Status Combined.

in the Row Labels area I have Project ID, Status

in values I Have a a sum


I'm trying to update the Reprot Filter field called P&L Start Date Status Combined based on my cell value in cell AS3 which I define. The code I have is below.


[pre]<br />
Dim pCriteria As String</p>
<p> Sheets("Pivot_Projects&#34.Select</p>
<p>'Applies the name value in cell AS3 to the filter for manager name<br />
pCriteria = Range("AS3&#34.Value</p>
<p>If pCriteria = "All" Then<br />
ActiveSheet.PivotTables("PivotTable2&#34.PivotFields( _<br />
"P&L Start Date Status Combined&#34.ClearAllFilters<br />
Else<br />
ActiveSheet.PivotTables("PivotTable2&#34.PivotFields("P&L Start Date Status Combined&#34.PivotFilters _<br />
.Add Type:=xlCaptionEquals, Value1:=pCriteria<br />
End If</p>
<p> Range("B2&#34.Select<br />
ActiveCell.FormulaR1C1 = "1"<br />



Everyting in there except the ELSE works. Is something wrong there? How can I get the ELSE to work. The first IF when it's ALL, the filter does clear out. It's the else that won't work.


I have another code which is the exact same except a different defined Critera name that works correctly. The only thing i notice is the Field is in the Row Label section and not the Report Filter Section of a pivot table. This code works 100% correct


<br />
Dim sCriteria As String</p>
<p> Sheets("Pivot_Projects&#34.Select</p>
<p>'Applies the name value in cell AM3 to the filter for manager name<br />
sCriteria = Range("AM3&#34.Value</p>
<p>If sCriteria = "All" Then<br />
ActiveSheet.PivotTables("PivotTable2&#34.PivotFields("Status&#34. _<br />
ClearAllFilters<br />
Else<br />
ActiveSheet.PivotTables("PivotTable2&#34.PivotFields("Status&#34.PivotFilters _<br />
.Add Type:=xlCaptionEquals, Value1:=sCriteria<br />
End If</p>
<p> Range("B2&#34.Select<br />
ActiveCell.FormulaR1C1 = "1"</p>
<p>[/pre]

 

[rjacmuto32]

Anybody have an idea for my problem jsut above this message. I can't get the code to update correctly with my pivot table filter in the reprot filter section vs the row label section