Vb Code Defined Name

[rjacmuto32]

I'm able to update a pivot table I have with VB code based on a value I put into my field. I'm using this code. It makes me able to change the filter on my name field.


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

.Add Type:=xlCaptionEquals, Value1:= "In-Progess"


My problem is I have to put in a code for what I want to filter at the end. If I want it to filter for Not Started, i have to change the code at the end to be = "Not Started" I have several different names I do that for depending on what someone selects from a cell.


Can I do this with a Defined Name Field?


For instance in cell L1 I have a formula that shows me my NAme of In-Progress, Not-Started, On Hold etc. That field I defined as MutoSelect. Since my field is now named how can I change the code above to filter on that defined named field?

 

[Luke M]

Sure thing. =)

[pre]

ActiveSheet.PivotTables("PivotTable2").PivotFields("Name").PivotFilters _
.Add Type:=xlCaptionEquals, Value1:= Range("MutoSelect").Value

[/pre]

 

[rjacmuto32]

That did't work. I stepped thru the code and it died at that line


This is my full code


Sub NameFilter()

'

' NameFilter Macro


Sheets("Sheet1").Select

ActiveSheet.PivotTables("PivotTable1").PivotFields("Name"). _

ClearAllFilters

ActiveSheet.PivotTables("PivotTable1").PivotFields("Name").PivotFilters _

.Add Type:=xlCaptionEquals, Value1:=Range("MutoSelect").Value


End Sub

 

[Luke M]

Assuming the cell L1 is on Sheet1, does this work?

[pre]

Sub NameFilter()
'
' NameFilter Macro
Dim xCriteria as String

Sheets("Sheet1").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Name"). _
ClearAllFilters
xCriteria = Range("L1").Value
ActiveSheet.PivotTables("PivotTable1").PivotFields("Name").PivotFilters _
.Add Type:=xlCaptionEquals, Value1:=xCriteria

End Sub

[/pre]

 

[rjacmuto32]

Same thing. The debugger stopped at that last line.


Is there a way to post a file in here?

 

[Luke M]

http://chandoo.org/forums/topic/posting-a-sample-workbook


Also, looks like this coding is unique to 2007/2010. I'm using 2003, so not sure how much more I can help. =(

 

[rjacmuto32]

Actually you know what I just found which is strange. I was thinking Name is sometimes a common or might be a reference to something else. (However in my example workbook, i only have the code above it listed. no other declarations or anything. Clean workbook)


I renamed my field from Name to Status. Updated my pivot table and changed your code to PivotFields("Status").PivotFilters etc. And it works now. Strange. Field name is Status and it works but with the fieldname of Name it didn't.


Anyway you code works great.

Thanks.

 

[Luke M]

Thanks for the info, glad you got it working. =)