VBA help needed to capture Error and Remove Pivot if error exist else resume

**Tescatlipoca** · 11-18-2014, 02:49 PM

Hi I am no programmer. By reading threads and such I created a spreadsheet which has multiple macros. Basically a report generator.

Excel version 2010

Current Setup:
On Load () I am forcing pivot tables to have certain filtering.

Problem:
When that filter is not available I am getting a VBA error. Or if I resume on Err. Filter is selected as whatever is available next (creates confusion on reporting)

Proposed Resolution:

I think I need to come up with a way where I check if error exist or not and move next. If exists I would like to remove the pivot table and move next, if no error than resume next.

However, I have no clue how to achieve this. Would really appreciate guidance.

Thank you!

**Bernie Deitrick** · 11-18-2014, 04:17 PM

If you post your code, that would be helpful. But you could try code like this with multiple error handlers, where you could customize the response to errors.

Sub PivotTableStuff()
    Dim PT As PivotTable

   On Error GoTo ErrHandler1
    
    Set PT = ActiveSheet.PivotTables(1)
    'code using PT
    PT.PivotFields("Category 1").PivotItems("Item 2").Visible = True
    
Err1Resume:
    
    On Error GoTo ErrHandler2
    Set PT = ActiveSheet.PivotTables(2)
    
    'code using PT
    PT.PivotFields("Category X").PivotItems("Item 3").Visible = True

Err2Resume:
    Exit Sub
    
ErrHandler1:
    MsgBox "error in 1"
    PT.ClearTable
    Resume Err1Resume
    
ErrHandler2:
    MsgBox "error in 2"
    PT.ClearTable
    Resume Err2Resume
    
End Sub

**Tescatlipoca** · 11-18-2014, 04:54 PM

Originally Posted by Bernie Deitrick

If you post your code, that would be helpful. But you could try code like this with multiple error handlers, where you could customize the response to errors.

Sub PivotTableStuff()
    Dim PT As PivotTable

   On Error GoTo ErrHandler1
    
    Set PT = ActiveSheet.PivotTables(1)
    'code using PT
    PT.PivotFields("Category 1").PivotItems("Item 2").Visible = True
    
Err1Resume:
    
    On Error GoTo ErrHandler2
    Set PT = ActiveSheet.PivotTables(2)
    
    'code using PT
    PT.PivotFields("Category X").PivotItems("Item 3").Visible = True

Err2Resume:
    Exit Sub
    
ErrHandler1:
    MsgBox "error in 1"
    PT.ClearTable
    Resume Err1Resume
    
ErrHandler2:
    MsgBox "error in 2"
    PT.ClearTable
    Resume Err2Resume
    
End Sub

Thank you Bernie, I am pasting the section of the code where I force the filters.

Sub Fix_Pivot()

On Error Resume Next

Sheets("PSD").Select
   'Update table 7 Number of PSDs scheduled by Month
        ActiveSheet.PivotTables("PivotTable7").PivotFields("Adhoc").CurrentPage = _
        "No"
        ActiveSheet.PivotTables("PivotTable7").PivotFields("Type").CurrentPage = _
        "PSD Inspection"
    'Update table 8 Number of PSDs scheduled by Month
        ActiveSheet.PivotTables("PivotTable8").PivotFields("Adhoc").CurrentPage = _
        "No"
        ActiveSheet.PivotTables("PivotTable8").PivotFields("Type").CurrentPage = _
        "PSD Inspection"
    'Update table 9 Number of PSDs overdue by Month
        ActiveSheet.PivotTables("PivotTable9").PivotFields("Type").CurrentPage = _
        "PSD Inspection"
        ActiveSheet.PivotTables("PivotTable9").PivotFields("Adhoc").CurrentPage = "No"
    'End of PSD tables

Sheets("Maint").Select
    'Update table 2 Number of Maintenance Activities by Month
        ActiveSheet.PivotTables("PivotTable2").PivotFields("Type").CurrentPage = _
        "Maintenance"
        ActiveSheet.PivotTables("PivotTable2").PivotFields("Adhoc").CurrentPage = "No"
    'Update table 3 Number of Maintenance Activity Results by Month
        ActiveSheet.PivotTables("PivotTable3").PivotFields("Type").CurrentPage = _
        "Maintenance"
        ActiveSheet.PivotTables("PivotTable3").PivotFields("Adhoc").CurrentPage = "No"
   'Update table 4 Number of Maintenance Activities Overdue by Month.
        ActiveSheet.PivotTables("PivotTable4").PivotFields("Type").CurrentPage = _
        "Maintenance"
        ActiveSheet.PivotTables("PivotTable4").PivotFields("Adhoc").CurrentPage = "No"
     'End of Maintenance tables
Sheets("AdHoc").Select
    'Update table 5 Number of Adhov Activities by Month.
        ActiveSheet.PivotTables("PivotTable5").PivotFields("Adhoc").CurrentPage = _
        "IsAdhoc"
        
    'Update table 5 Number of Adhoc Activity Results by Month.
        ActiveSheet.PivotTables("PivotTable6").PivotFields("Adhoc").CurrentPage = _
        "IsAdhoc"
     'End Update Filters

End Sub

On your code above, how do I go to the next pivot table, as seen from my code I have 8 pivot tables.

**Bernie Deitrick** · 11-18-2014, 05:17 PM

This is how I would handle the first 3 (7 through 9)

Sub Fix_Pivot2()
    Dim PT As PivotTable
    Dim i As Integer
    
    With Sheets("PSD")
        'Update table 7, 8, and 9 Number of PSDs scheduled by Month
        On Error GoTo ErrH7
        For i = 7 To 9
            Set PT = .PivotTables("PivotTable" & i)
            
            PT.PivotFields("Adhoc").CurrentPage = _
            "No"
            PT.PivotFields("Type").CurrentPage = _
            "PSD Inspection"
Resume7:
        Next i
    End With
    Exit Sub
    
ErrH7:
    PT.ClearTable
    Resume Resume7
    
End Sub

**Tescatlipoca** · 11-18-2014, 05:23 PM

Thank you for your help!

**Tescatlipoca** · 11-18-2014, 05:46 PM

Just an update tested, and works like a charm! thank you for your help!

VBA help needed to capture Error and Remove Pivot if error exist else resume

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA help needed to capture Error and Remove Pivot if error exist else resume

Re: VBA help needed to capture Error and Remove Pivot if error exist else resume

Re: VBA help needed to capture Error and Remove Pivot if error exist else resume

Re: VBA help needed to capture Error and Remove Pivot if error exist else resume

Re: VBA help needed to capture Error and Remove Pivot if error exist else resume

Re: VBA help needed to capture Error and Remove Pivot if error exist else resume

Thread Information

Users Browsing this Thread

Similar Threads

Error pops up on line On Error Resume Next

Error 1004 impossible to capture with On Error go To

Resume on Error?

[SOLVED] on error resume next

On Error Resume Next

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions