VBA Macro Bypass Missing Filtered Item and Resume to Next Line

  • Hi, I am trying to create a macro code to automatically find a range within transactions for multiple excel worksheets. However, worksheets don't always have the same pivot items. For example, the VBA code below is trying to filter on Field 13 to find "Borrowed and Lent", but there is way to bypass it when the filtered item is not available and just resumes to the next line?


    [VBA]
    Sub FilterMissingItem


    Dim pt As PivotTable
    Dim pf As PivotField
    Dim Pi As PivotItem


    Sheets("GL").Select
    ActiveSheet.Range("$A$1:$AJ$1").AutoFilter Field:=13, Criteria1:= _
    "Borrowed and Lent"
    Sheets("Sheet1").Select
    Set pt = ActiveSheet.PivotTables("NetZero")
    Set pf = pt.PivotFields("PA_EXPENDITURE_ID")


    'pf.ClearAllFilters
    'pf.PivotFilters.Add xlValueIsBetween, pt.DataFields(1), -0.001, 0.001


    Range("C5").Select
    ActiveCell.FormulaR1C1 = "=IF(AND(RC[-1]<0.001,RC[-1]>-0.001),2,""#N/A"")"
    'Range("C6").Select
    'ActiveCell.FormulaR1C1 = "2"
    Range("C5").Select
    Selection.AutoFill Destination:=Range("C5:C" & Cells(Rows.Count, "A").End(xlUp).Row)


    Sheet1.Select
    Range("A5:C5").Select
    Range("C5").Activate
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Names.Add Name:="MyTable3", RefersToR1C1:= _
    "=Sheet1!R5C1:R50000C3"


    Sheets("GL").Select
    ActiveSheet.Range("A1").AutoFilter Field:=24, Criteria1:="="
    'Range("X4").Select


    End Sub
    [/VBA]


    Thanks!
    Wayne

  • You posted this in the "Hire / Help" forum. This forum is for asking for and getting help that you PAY for. Moving this to the appropriate forum.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!