error handling when updating a pivlt table where there is no data

[Legacy 139986]

I have a macro that updates a series of pivot tables based on a field selection in the pivot table, for example, in the page filter I select the 'sales region' (North, South, East, or West) and in the 'product' (apples, oranges, pears or bananas). It works great as long as there is data to display. If there is no data for bananas in the North region the macro errors out and displays the end or debug message. There almost always is no data for bananas in the North (keyword 'almost'). What can I add to my macro that will say 'If there is no data, display a message that says 'No data for the selected item' and defaults to 'All' products? Needless to say my problem is a little more complicated than this in that there are several macros for several combinations but the problem is the same for each of them if there is no data to display. I hope someone can help.

 

[Legacy 139986]

I have a macro that updates a series of pivot tables based on a field selection in the pivot table, for example, in the page filter I select the 'sales region' (North, South, East, or West) and in the 'product' (apples, oranges, pears or bananas). It works great as long as there is data to display. If there is no data for bananas in the North region the macro errors out and displays the end or debug message. There almost always is no data for bananas in the North (keyword 'almost'). What can I add to my macro that will say 'If there is no data, display a message that says 'No data for the selected item' and defaults to 'All' products? Needless to say my problem is a little more complicated than this in that there are several macros for several combinations but the problem is the same for each of them if there is no data to display. I hope someone can help.

I'm guessing that I'm not being specific enough to generate a response so I'll try to do better. I work in a health care environment which is why I'm being a little cryptic. I have a series of pivot tables set up on a worksheet for a specific care facility. One for admits, one for discharges, one for type of treatment, and so on. I then have several buttons representing different insurance companies. Each button has a macro attached that changes the page filter for all of the pivot tables to that insurance company and displays the counts accordingly. I got the code from a forum that helped me create the structure for each named pivot table to first remove any filters then cycle through each pivot table and set the desired filter.

ActiveSheet.PivotTables("tablename").PivotFields("Insurance Co"). _
ClearAllFilters
ActiveSheet.PivotTables("tablename").PivotFields("Insurance Co"). _
CurrentPage = "[name of ins co]"

It all works great. The problem is if there is no data for a particular insurance company that name isn't available in the filter dropdown which is where the error occurs. I would like to add/modify the code so that if the desired insurance company isn't in the dropdown the pivot table either displays 0 (first preference) or defauls to [All] with a message stating "No data exists for that company." or something like that. I'm hoping that eventually I'll be able to condense this all down to a single macro that instead of having a macro for each insurance company will pick up the variables from a picklist. I know enough VBA to know it can be done but not enough to know how to do it. Thanks for any help.