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