Star Key
08-28-2007, 11:00 PM
I'm just a beginner at this and am not sure how to proceed. What I need is to get the autofilter to automatically select the next criteria, without actually specifying what the criteria is, until the end of the criteria list. i.e. in the example below, instead of Criteria1:=B, Criteria1:=C, etc, as the next criteria, I would like it to just go on to the next criteria in the list until the list runs out. This is what i want to use it in.
Sub Autofilter_progressive()
Selection.AutoFilter Field:=10, Criteria1:="A'
Application.Goto Reference:="R1C5"
Selection.End(xlDown).Select
Range("E18:I18").Select
Selection.Copy
ActiveSheet.Previous.Select
Application.Goto Reference:="R2C2"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False
Range("B3").Select
ActiveSheet.Next.Select
Selection.AutoFilter Field:=10, Criteria1:="B"
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Previous.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False
Range("B4").Select
ActiveSheet.Next.Select
Selection.AutoFilter Field:=10, Criteria1:="C"
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Previous.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False</p> Range("B5").Select
ActiveSheet.Next.Select
Selection.AutoFilter Field:=10, Criteria1:="D"
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Previous.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False
Range("B6").Select
ActiveSheet.Next.Select
End Sub
Looking forward to the solution.Thanks
Sub Autofilter_progressive()
Selection.AutoFilter Field:=10, Criteria1:="A'
Application.Goto Reference:="R1C5"
Selection.End(xlDown).Select
Range("E18:I18").Select
Selection.Copy
ActiveSheet.Previous.Select
Application.Goto Reference:="R2C2"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False
Range("B3").Select
ActiveSheet.Next.Select
Selection.AutoFilter Field:=10, Criteria1:="B"
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Previous.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False
Range("B4").Select
ActiveSheet.Next.Select
Selection.AutoFilter Field:=10, Criteria1:="C"
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Previous.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False</p> Range("B5").Select
ActiveSheet.Next.Select
Selection.AutoFilter Field:=10, Criteria1:="D"
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Previous.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False
Range("B6").Select
ActiveSheet.Next.Select
End Sub
Looking forward to the solution.Thanks