Help with VBA coding

[yitm03]

[h=2]Hi there, Stuck with an issue for over a week and need someone to point me in the right direction. I recorded a macro to do a advanced filter and copy the data to another sheet. Works great with one drawback! I have over 3000 rows and created Do While Loop to get the values into 2 different columns but the macro had a fixed range from doing it manually and obviously it just creates 3000 sheets with the same value.
Sub calcModO()
Dim row As Integer
row = 2

Do While Cells(row, 6) <> ""
Cells(row, 7).Value = Cells(row, 9).Value * 0.85
Cells(row, 8).Value = Cells(row, 9).Value * 1.15

Sheets.Add After:=ActiveSheet
Range("A1").Select

Sheets("sheet1").Range("A1:AF1262").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("DIA (4)").Range("G2:H2"), CopyToRange:=Range( _
"A1"), Unique:=False

row = row + 1
Loop

End Sub

How can I, in the loop have the CriteriaRange:=Sheets("sheet1").Range("G2:H2"), change to the next row ("G3:H3")? Do I need to call on a procedure or can it be done within the code above?
Thanks for any much needed hep.[/h]

 

[offthelip]

change the criteria line to something like this: ( not Tested!!)

CriteriaRange:=Sheets("DIA (4)").Range("G" & Row & ":H & Row ), CopyToRange:=Range( _

 

[yitm03]

change the criteria line to something like this: ( not Tested!!)

CriteriaRange:=Sheets("DIA (4)").Range("G" & Row & ":H & Row ), CopyToRange:=Range( _

.

Thanks for the reply. I made the change but now getting a compile error with that change. Thanks all the same, much appreciated.

 

[offthelip]

My fault I don't usually use that addressing mode I write it like this which use columns numbers which I find easier because it is easier to use indices: try this:

CriteriaRange:=Sheets("DIA (4)").Range(Cells(Row, 7), Cells(Row, 8)), CopyToRange:=Range( _

I have managed to crack the compile error by doing the following:

Dim txt As String
txt = "G" & Row & ":H" & Row


Sheets("DIA (4)").Range(txt), CopyToRange:=Range( _

[/CODE]