Copying every other row

[Guest]

Hi,

Starting in cell C16 I need to copy every 3rd cell in column C until there
is no more data. How do I copy cell C16 , offset down 2 cells, copy again etc
etc until blank.

I then need to paste all these values into column C of another worksheet
("LFmacro.xls" sheet name "report") - BUT this worksheet already has data in
it so would need to paste into the next blank row (or leave a blank row and
paste into the next would be useful)

Can anyone help me?

 

[Don Guillett]

might be best to work from the bottom up, something like

for i= cells(rows.count,"a").end(xlup).row to 1 step -3
cells(i,"a").copy sheets("dest").
cells(cells(rows.count,"a").end(xlup).row+1,"a")
next i

 

[Guest]

Hi Don,
Thanks for your reply, I tried this out but think I've broken the code onto
a new row or something...
Is it supposed to be 2 lines of code both starting with Cells??

I get an error on this line:
Cells(i, "C").Copy Sheets("dest").Cells(Cells(Rows.Count, "C").End(xlUp).Row
+ 1, "C")

I changed all the "A" to "C" - I assume this is right if I'm working with
Column C, also, how does it know to stop at C16??

I just want to get this to copy at the moment, I've got code now to paste
into the next blank row in the destination spreadsheet.
Thanks

 

[Guest]

Also by starting at the bottom you copy the wrong information! I need to
start at C16 then copy every third line, or if starting at the bottom I need
to go up one row then copy that cell and every third cell above it.
(Otherwise I'm copying the wrong thing)

Also this pastes into column C in the same sheet, I need to go to 'Report'
sheet in "LFmacro.xls". Where would I put this into the code???

Thanks,

 

[Don Guillett]

Replace yours with this to copy and paste. Put in a module and run from your
source sheet.

Sub copyeverythirdA()
For i = 1 To Cells(Rows.Count, "c").End(xlUp).Row Step 3
With Workbooks("LFmacro.xls").Sheets("Report")
lr = .Cells(Rows.Count, "a").End(xlUp).Row + 1
Cells(i, "c").Copy .Cells(lr, "a")
End With
Next i

End Sub

 

[Guest]

Thanks Don, this works great,
I've solved the problem of having to stop the code at cell C16 by assuming
the file format I receive is standard thus deleting rows 1:15 beforehand.
Thank you

 

[Don Guillett]

I overlooked that part (start at row 16).
1 To Cells(Rows.Count, "c").End(xlUp).Row Step 3
to
16 To Cells(Rows.Count, "c").End(xlUp).Row Step 3