Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy Paste Macro (Excel 2003)

    Hello I am trying to do the following. Can some one help as I have searched and could not find anything. Thanks

    Select Cells A1:I1
    Copy to Cells A2:I2
    Select Cells A3:I3
    Copy to Cells A4:I4
    Select Cells A5:I5
    Copy to Cells A6:I6
    ......
    Select Cells A600:I600
    Copy to Cells A601:I601

    Thank you

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copy Paste Macro (Excel 2003)

    Hi There

    This should do the job that you describe:

    <pre>Sub CopyMeDown()


    For i = 600 To 1 Step -1

    Range("A" & i & ":" & "I" & i).Select

    Selection.Cut

    Range("A" & i + 1).Select
    ActiveSheet.Paste
    Next i
    End Sub</pre>

    Jerry

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copy Paste Macro (Excel 2003)

    Actually

    Thinking about it, you can do it in just one step, it just depends if you want to impress the boss....if so do the first one <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    If not this will do it:

    Sub CutMeDown()

    Range("A1:I600").Select
    Selection.Cut
    Range("A2").Select
    ActiveSheet.Paste
    End Sub
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy Paste Macro (Excel 2003)

    Jerry, your code cuts and pastes instead of copies and pastes; moreover it doesn't act on odd rows only, as requested by the original poster. The effect is that A1:I600 is moved down one row - this could have been accomplished without a loop.

    And i is not declared explicitly.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copy Paste Macro (Excel 2003)

    This is a little different than Jerry's. It only works on every other row which is what I deduce from from your description.

    <pre>Sub CopyMeDown()
    Dim i As Integer
    For i = 1 To 600 Step 2
    Range("A" & i & ":" & "I" & i).Copy _
    Range("A" & i + 1)
    Next
    End Sub</pre>


    Steve

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy Paste Macro (Excel 2003)

    You want to copy odd rows, so you won't end at row 600, but either at row 599 or at row 601. Try this variation on Jezza's code:

    Sub CopyMeDown()
    Dim i As Integer
    For i = 1 To 600 Step 2
    Range("A" & i & ":" & "I" & i).Copy Destination:=Range("A" & (i + 1))
    Next i
    End Sub

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copy Paste Macro (Excel 2003)

    Yes I see your point but the message is confused as A600 is even not odd, so I think there maybe a glitch in the logic so I made a suggestion hence why I step backwards and then it doesn't matter if it is odd or even as I would be cutting and pasting blank rows <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>....a little change in tact got me thinking about my second offering similar to what Legare has offered <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy Paste Macro (Excel 2003)

    Legare? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copy Paste Macro (Excel 2003)

    Legare? I am sure I saw him around with a copy and a paste....me thinks he has deleted his post in the interim <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    Jerry

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Paste Macro (Excel 2003)

    I deleted my post since I realized immediately after posting that I was not operating on just odd numbered rows as the OP requested. My code was similar to Jerry's second post except I didn't select before copying and pasting.
    Legare Coleman

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy Paste Macro (Excel 2003)

    Thanks - I was wondering what was happening... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  12. #12
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Paste Macro (Excel 2003)

    I think hans has the right idea.

    Currently there is data is in cell A1:I418.

    However, Hans I don't understand your conventions in your formula a I am a newbie.

    Can you please revise the formula with the above range?

    Thank you.

  13. #13
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copy Paste Macro (Excel 2003)

    Hans Looks busy so I will help you here

    Sub CopyMeDown()
    Dim i As Integer
    For i = 1 To 418 Step 2
    Range("A" & i & ":" & "I" & i).Copy Destination:=Range("A" & (i + 1))
    Next i
    End Sub
    Jerry

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Paste Macro (Excel 2003)

    You should not have to change anything. If there is no data after row 418, then the macro is just going to move some empty rows around which should not hurt anything. Then you won't have to change the macro every time you add more rows. If you know that there will always be data in column A in the last used row, you can let the macro calculate where the last data is like this:

    <code>
    Sub CopyMeDown()
    Dim i As Integer
    For i = 1 To Range("A65536").End(xlUp).Row - 1 Step 2
    Range("A" & i & ":" & "I" & i).Copy Destination:=Range("A" & (i + 1))
    Next i
    End Sub
    </code>
    Legare Coleman

  15. #15
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Paste Macro (Excel 2003)

    This worked except for it copied every worksheet in the workbook. Suppose I only wanted to do one worksheeet called sales. How do I modify the macro?

    Thank you.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •