For some reason I am receiving the following error when trying to write an advanced filter "Method 'Range' of object '_Global' Failed"
Weird thing is I have virtually the same code(the dynamic range and advanced filter part) in a another macro with just different names and it works fine there. The purpose of the filter is to display on the FICA OOBs sheet only items in the WT column of /403, /404, /405, /406 and the FICA OOBs column with a value < 0 using the criteria on the Filter_Criteria sheet. The range of the filter is a named Range I created based on the FICA OOBs sheet.
Sub FICA_OOB_EE_SS_Table()
'
'Verifies that all EE SS FICA wagetypes are being calculated correctly
'
'Prevent screen updating
Application.ScreenUpdating = False
Call DeleteNamedRanges
Call FICA_Filter_Criteria_Sheet
'Create copy of DAT sheet
Sheets("DAT").Copy After:=Sheets("DAT")
ActiveSheet.Name = "FICA OOBs"
'enter formula in column
Sheets("FICA OOBs").Select
Columns("N:N").Formula = "=J1*FICA_Rates!$B$2-F1"
Range("N1") = ""
Range("N2") = "FICA OOBs"
'Create Dynamic named range
ActiveWorkbook.Names.Add Name:="RANGE_OOBs", RefersToR1C1:= _
"=OFFSET(FICA OOBs!R2C1,0,0,COUNTA(FICA OOBs!C2),14)"
ActiveWorkbook.Names("RANGE_OOBs").Comment = ""
'Apply advanced filter
Range("RANGE_OOBs").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Filter_Criteria").Range("A1:B5"), Unique:=False 'debugger points to this line
Range("A4").Select
Rows(2).Font.Bold = True
'autofit column width
Columns("N:N").EntireColumn.AutoFit
Range("N1") = ""
Range("N2") = "FICA OOBs"
'Hide Columns
Sheets("FICA OOBs").Select
Range("C1,G1:I1,K1:M1").EntireColumn.Select
Selection.EntireColumn.Hidden = True
End Sub
Sub FICA_Filter_Criteria_Sheet()
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Filter_Criteria"
ActiveCell.FormulaR1C1 = "Filter_Criteria"
Range("A1") = "WT"
Range("A2") = "/403"
Range("A3") = "/404"
Range("A4") = "/405"
Range("A5") = "/406"
Range("B1") = "FICA OOBs"
Range("B2:B5") = "<0"
Rows(1).Font.Bold = True
'autofit column width
Columns("A:B").EntireColumn.AutoFit
End Sub
Thanks for nay help,
Craig
Bookmarks