Paste every other cell (Excel 2003)

Topic

New Reply

I have a need to copy cells A1, A3, A5 and I want to past them in A101, A103, and A105. I know I can copy all three at once by holding down the ctrl key, but when I go to paste they paste in A101, A102, A103. Is there a way to have them paste in the proper cells (every other row). Thank you.

Reply | Quote

Replies

You could do it with a macro:

Public Sub CopyThree()
    ActiveSheet.Range("A1").Copy Destination:=Range("A101")
    ActiveSheet.Range("A3").Copy Destination:=Range("A103")
    ActiveSheet.Range("A5").Copy Destination:=Range("A105")
End Sub

Reply | Quote

I did not want to use a macro because the amount of cells to copy and paste will vary throughout this spreadsheet. Thank you anyway.

Reply | Quote

Is the location to paste always 100 rows below the first copied cell? If so, some slightly more complicated code could handle it.

Public Sub CopyToRow100()
Dim oCell As Range
    For Each oCell In Selection
        oCell.Copy Destination:=oCell.Offset(100, 0)
    Next oCell
End Sub

Reply | Quote

That’s it. Thank you.

Reply | Quote

Select A1:A5 (the entire range, not the individual cells)
Copy
Select A101
Paste

You can then delete A102 and A104 if A2 and A4 had something in them.

Steve

Reply | Quote

Won’t that also copy A2 to A102 and A4 to A104?

Reply | Quote

I had (incorrectly) assumed that A2 and A4 were blank, and after posting realized that might not be correct, and then edited my post to delete A102 and A104.

But even that is incorrect, and you have already posted a good solution.

Steve

Reply | Quote

No, this will not work because there are different formulas in A102 and A104 and A106 that would be destroyed with this approach. Thank you.

Reply | Quote