troubleshooting Question

VBA Pivot Field Filter Error

Avatar of David Edwards
David Edwards asked on
Microsoft OfficeMicrosoft ExcelVBA
7 Comments2 Solutions296 ViewsLast Modified:
Hi there, I have written a VBA procedure (well multiple as its very large code) which basically re-creates a large number of Pivot tables from the source data sheet so as the source data changes, I delete & rebuild the Pivots.  All of this is working fine.  

However, in the source data, there is a column called "Region" and there are 5 main regions consisting of CEE, DACH, UK/IE, NL & CENTER.  I have noticed towards the very end of coding, that in some cases, this approach is generating an error and its where the source data for the Pivot, does not actually contain a specific region

For example, if I copy the VBA for the multiple Pivots I created for NL region, I have been setting the region filters in VBA as follows:-

'Add Region Filter
With .PivotFields("Region")
        .PivotItems("CEE").Visible = False
        .PivotItems("DACH").Visible = False
        .PivotItems("UK/IE").Visible = False
        .PivotItems("CENTER").Visible = False
        .PivotItems("(blank)").Visible = False
End With

The above works fine so long as in the source data for the Pivot I am creating, all of the values above are present, if for example, in the Pivot source data, there is no data for "UK/IE", the VBA throws an error, rather than simply skipping over the appropriate line of code.

Is there an easy / quick resolution sticking with the above structure, to get VBA to ignore a filter request if the data does not exist in the source ( I do not really want to re write all of the code - it really is quite massive) ?

Thanks in advance
Excel VBA Developer
Join our community to see this answer!
Unlock 2 Answers and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros