Copying and Pasting in a Loop
 

Janos,

Try:

Sub TEST()

Const limitDOWN As Date = #1/1/2007#
Const limitUP As Date = #2/2/2007#
Const isDOMESTIC As String = "Y"
Dim c As Range
Dim d As Range

For Each c In Worksheets("Instructions").Range("E7:E200").Cells
If c.Value limitDOWN And c.Value < limitUP And _
c.Offset(0, -3).Value = isDOMESTIC Then
c.Resize(1, 7).Copy
Worksheets("Janos").Range("A65536").End(xlUp)(2).P asteSpecial
Paste:=xlValues
End If
Next c

MsgBox "All done!"

End Sub

HTH,
Bernie
MS Excel MVP


"Janos" wrote in message
...
Hello, once again I implore the help of the Excel gods oin the ether!
I'm so sorry to ask another silly question, but how do I copy and then
paste
ranges in a loop. The idea is that once i check for two conditions to be
true, i need to copy that range and paste it into another (existing)
worksheet. I tried to do it with a nested loop, but it's not working. See
beklow for code.

Any ideas?

Thank you very much!

Sub TEST()

Const limitDOWN As Date = #1/1/2007#
Const limitUP As Date = #2/2/2007#
Const isDOMESTIC As String = "Y"
Dim c As Range
Dim d As Range

For Each c In Worksheets("Instructions").Range("E7:E200").Cells
If c.Value limitDOWN And c.Value < limitUP And _
c.Offset(0, -3).Value = isDOMESTIC Then
With c.Range(Cells(1, 1), Cells(1, 7)).Copy
For Each d In Worksheets("Janos").Range("A5").Cells
d.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Next d
End With
End If
Next c

MsgBox "All done!"

End Sub