Macro Run-time Error for a pivot table

[jme1013]

Hello,

I have a macro that controls 3 different pivot tables for me. It works fine as long as the filter "term" is in the data. The data gets updated monthly and sometimes some of the "terms" that I use to filter with won't have any data for a given month.

When this happens I get the 1004: run-time error Application-defined or object-defined error, due to the Filter "term" being absent.

here's my code...

Sub MXG()
'
' MXG Macro
' Get MXG Data
'
'
    Sheets("My Data").Select
    ActiveSheet.PivotTables("PivotTable13").PivotFields("Work Center"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable13").PivotFields("Work Center").CurrentPage _
        = "MXG"
    ActiveSheet.PivotTables("PivotTable14").PivotFields("Org").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable14").PivotFields("Org").CurrentPage = "MXG"
    ActiveSheet.PivotTables("PivotTable15").PivotFields("Org").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable15").PivotFields("Org").CurrentPage = "MXG"
End Sub

In this example, if there were no data points in a given month for MXG, then "MXG" will not be in the pivot table to picked, causing the error.

I'd like for it to pick "(blank)" in the absence of "MXG" because "(blank)" is always available.

I'd need it to do this for all 3 pivot tables separately.

 

[Joe4]

Cross-posted here: Runtime Error 1004 - Due to missing data

Note that while we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[jme1013]

Thank you.

Runtime Error 1004 - Due to missing data

Hi, I'm having an issue with a macro that I get a run-time error '1004' Application-defined or object-defined error This happens when I run a macro that controls 3 different pivot tables. As long as the pivot tables contain the data that I'm wanting it to be filtered by, everything...

www.excelforum.com

I've cross-posted this trying to cast a wide net.