umasriram2
01-22-2009, 10:49 AM
I’m building a macro that can do an advanced filter and copy the resultant in another sheet. However I get a run time error 1004 in the filtering line. Could you please help? Here is my code
Sub filter()
Dim crit As Range
Dim rng As Range
Worksheets("criteria").Select
Set crit = Sheets("criteria").Range("A2", Range("A2").End(xlDown))
Sheets("Unprocessed").Select
Set rng = Sheets("Unprocessed").Range("A1", Range("A1").End(xlDown).End(xlToRight))
Cells.Select
Range(rng).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets( _
"criteria").Range(crit), Unique:=False
ActiveWindow.SmallScroll Down:=-42
Selection.Copy
Sheets.Add
ActiveSheet.Paste
Sheets("criteria").Select
Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Processed"
Range("B8").Select
Sheets("Sheet5").Select
Sheets("Sheet5").Name = " Processed "
Range("I18").Select
Sheets("Unprocessed").Select
Cells.Select
ActiveSheet.ShowAllData
ActiveWorkbook.Save
End Sub
Edit Lucas: vba tags added to code. You can select your code when posting and hit the vba button to format your code for the forum.
Sub filter()
Dim crit As Range
Dim rng As Range
Worksheets("criteria").Select
Set crit = Sheets("criteria").Range("A2", Range("A2").End(xlDown))
Sheets("Unprocessed").Select
Set rng = Sheets("Unprocessed").Range("A1", Range("A1").End(xlDown).End(xlToRight))
Cells.Select
Range(rng).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets( _
"criteria").Range(crit), Unique:=False
ActiveWindow.SmallScroll Down:=-42
Selection.Copy
Sheets.Add
ActiveSheet.Paste
Sheets("criteria").Select
Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Processed"
Range("B8").Select
Sheets("Sheet5").Select
Sheets("Sheet5").Name = " Processed "
Range("I18").Select
Sheets("Unprocessed").Select
Cells.Select
ActiveSheet.ShowAllData
ActiveWorkbook.Save
End Sub
Edit Lucas: vba tags added to code. You can select your code when posting and hit the vba button to format your code for the forum.