jeremycole32
12-15-2014, 05:55 PM
Hi, I am currently trying to write a macro which creates two pivot tables on two separate sheets, with a unique filter applied to each. My problem arises in the fact that the filter that needs to be applied is dynamic in the sense that no two data sets will be the same. Essentially, I'm trying to create one pivot table filtered on New business and one pivot table filtered on Renew business, but the filter will need to look just for the phrase "New" or "RNw" in the plan element box, as there are hundreds of different combinations that can go in front of those words. The code I'm trying to use is below, but it keeps giving me the error "Run-time error '1004': Application-defined or object-defined error". Any suggestions would be much appreciated!!
'Filter on specific plan element
PT.ClearAllFilters
PT.PivotFields("Plan Element").PivotFilters.Add Type:=xlCaptionContains, Value1:="New"
'Filter on specific plan element
PT.ClearAllFilters
PT.PivotFields("Plan Element").PivotFilters.Add Type:=xlCaptionContains, Value1:="New"