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?

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

--
Don Guillett
SalesAid Software

"Meltad" wrote in message
...
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?

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



"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"Meltad" wrote in message
...
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?

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,



"Meltad" wrote:

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



"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"Meltad" wrote in message
...
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?

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


--
Don Guillett
SalesAid Software

"Meltad" wrote in message
...
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,



"Meltad" wrote:

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



"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"Meltad" wrote in message
...
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?

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" wrote:

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


--
Don Guillett
SalesAid Software

"Meltad" wrote in message
...
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,



"Meltad" wrote:

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



"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"Meltad" wrote in message
...
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?

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


--
Don Guillett
SalesAid Software

"Meltad" wrote in message
...
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" wrote:

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


--
Don Guillett
SalesAid Software

"Meltad" wrote in message
...
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,



"Meltad" wrote:

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



"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"Meltad" wrote in message
...
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?