Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying every other row
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying every other row
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying every other row
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying every other row
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying every other row
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying row to row | Excel Discussion (Misc queries) | |||
copying the Hyperlink function result without copying the actual formula | Excel Worksheet Functions | |||
TextBox copying not like windows copying, heh? | Excel Programming | |||
Copying | Excel Programming | |||
Copying data down to next dirty cell, then copying that data | Excel Programming |