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
|