Macro to Create Multiple Pivot Tables in Multiple Sheets with Dynamic Data Filter

[jeremycole32]

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"

[Kenneth Hobs]

Looks ok to me. Maybe your setting of the PT object did not set. Try a

Debug.Print PT.Name

[jeremycole32]

Hi Kenneth. Thanks for the suggestion but it still throws the same error. It is worth mentioning that I'm trying to apply this filter at the Report Filter level. I was trying to think of a different way around this issue. Maybe looking at the raw data before it is pivoted, find the caption "New" or "RNw" within that data set, and assign that plan name to a variable. This way it is filtering on exactly what the plan element name is. I'm just trying to figure out the best way to look for a caption within a column and when it finds it assigning that cell value to a variable so that it would look like this:

Dim XVar as String (where XVar is the value of the cell that contains a specific string)

ActiveSheet.PivotTables("PivotTable1").PivotFields("Plan Element").CurrentPage _
= XVar

Any suggestions or advice would be greatly appreciated.