For loop problem

[Michaelpfreem]

Hello all, I am having a little for loop problem (see code below)

For Crit1 = 1 To 42
For Crit2 = 2 To 43
Sheets("SortData").Select
Selection.AutoFilter Field:=1, Criteria1:=">=" & Crit1, Operator:=xlAnd, _
Criteria2:="<=" & Crit2
Range("A2:B20000").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("I1:AV1").Select
Selection.Copy
Sheets("Sheet3").Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Sheet2").Select
Range("A2:B20000").Select
Selection.ClearContents
Next Crit2
Next Crit1
As you can see I am trying to get to different numbers (crit1 and crit2) to loop through a given number of changes. I have up and till now just been repeating this code and changing the numbers in the line auotfilter line. this leads to huge macros. I know you can write for loops to change one number at a time, in fact that is all that seems to happen with this macro only crit2 changes, crit1 stays the same.

So i am wondering is it possible to get the for loop to change both unmbers that I need for my autofiltering to work.

Many thanks in advance,
Mike

 

[VoG]

Perhaps (untested)

For Crit1 = 1 To 42
    Crit2 = Crit1 + 1
    Sheets("SortData").Select
    Selection.AutoFilter Field:=1, Criteria1:=">=" & Crit1, Operator:=xlAnd, _
    Criteria2:="<=" & Crit2
    Range("A2:B20000").Copy Destination:=Sheets("Sheet2").Range("A2")
    Sheets("Sheet2").Range("I1:AV1").Copy
    Sheets("Sheet3").Select
    ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("Sheet2").Range("A2:B20000").ClearContents
Next Crit1
Application.CutCopyMode = False

 

[Michaelpfreem]

Thank you very much mate you are a hero, that code is also a little bit cleaner as well, I will have to take note of the way you arranged some of those lines for future use.

Thanks again