Location to paste, quick question

[Desu Nota from Columbus]

I have the following code:

Dim i As Integer

For i = 16 To Range("L" & Rows.Count).End(xlUp).Row
    If Range("L" & i).Value = False Then
        Range("A" & i & ":" & "K" & i).Copy
            With Sheets("Sheet2")
                Range("A" & i).Paste
            End With
    End If
Next i

Inside the with statement, the range isn't accepted. I am copying values from 1 sheet, and then trying to have them paste in column A without copying over eachother.

I originally had (instead of the with statement):

Sheets("Sheet2").Paste

And all the values pasted to A1 so only 1 value was seen (because they pasted over one another). I've tried a plethora of different syntaxes but nothing I've tried thus far has worked.



How do you specify "paste to the first open cell in Column A, sheet2"?

 

[njimack]

I think you're making it too complicated. Try this:

Dim i As Integer
 
For i = 16 To Range("L" & Rows.Count).End(xlUp).Row
 
If Range("L" & i).Value = False Then Range("A" & i & ":" & "K" & i).Copy Sheets("Sheet2").Range("A" & i)
 
Next i

 

[Desu Nota from Columbus]

Is their an implied TO in this statement?


If Range("L" & i).Value = False Then Range("A" & i & ":" & "K" & i).Copy TO Sheets("Sheet2").Range("A" & i)

Im just trying to understand why you don't have to give a direction after saying "copy".

Thanks for the help.

 

[njimack]

There's an implied Destination. The full code would be:
If Range("L" & i).Value = False Then Range("A" & i & ":" & "K" & i).Copy Destination: Sheets("Sheet2").Range("A" & i)

Also, it's sometimes easier to refer to ranges using Cells, e.g

If Cells(i,12).Value = False Then Range(Cells(i,1),Cells(i,11)).Copy Sheets("Sheet2").Cells(i,1)

 

[Desu Nota from Columbus]

I see. Is there an advantage/disadvantage to referring directly to cells?

 

[njimack]

I see. Is there an advantage/disadvantage to referring directly to cells?

I'm not sure to be honest. I personally find it easier, and I think it's easier on the eye, since you don't have to include quote marks or colons.