[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]
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]