+ Reply to Thread
Results 1 to 13 of 13

Need Simple Copy and Insert Row, Move Down Macro

  1. #1

    Need Simple Copy and Insert Row, Move Down Macro

    Hello,

    Is there simple macro to copy and insert existing row data and then move
    down and repeat for all rows?

    Example needed:

    Rows existing
    1
    2
    3
    4

    Rows after Macro
    1
    1
    2
    2
    3
    3
    4
    4

    Thanks

  2. #2
    dmexcel
    Guest

    Re: Need Simple Copy and Insert Row, Move Down Macro

    I just used the Macro Recorder and got this, this macro starts at A1
    and copies to A6:A7
    then goes to A2 and copies to A8:A9
    and so on, this will work if you only have a few lines to copy, if you
    have many lines then we can do a do until code, if you require that
    then let us know




    Sub CopyDblLines()
    '
    ' CopyDblLines Macro
    ' Macro recorded 2/23/2006 by Dave
    '

    '
    Range("A1").Select
    Selection.Copy
    ActiveCell.Offset(5, 0).Range("A1:A2").Select
    ActiveSheet.Paste
    ActiveCell.Offset(-4, 0).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(6, 0).Range("A1:A2").Select
    ActiveSheet.Paste
    ActiveCell.Offset(-5, 0).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(7, 0).Range("A1:A2").Select
    ActiveSheet.Paste
    ActiveCell.Offset(-6, 0).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(8, 0).Range("A1:A2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveCell.Offset(-7, 0).Range("A1").Select
    End Sub


  3. #3
    Tom Ogilvy
    Guest

    Re: Need Simple Copy and Insert Row, Move Down Macro

    Sub AddRows()
    Dim lasrow As Long, i As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = lastRow To 1 Step -1
    Rows(i + 1).Insert
    Cells(i + 1, 1).Value = Cells(i, 1).Value
    Next
    End Sub


    worked for me with your numbers in column 1 starting in A1

    --
    Regards,
    Tom Ogilvy


    <[email protected]> wrote in message
    news:[email protected]...
    > Hello,
    >
    > Is there simple macro to copy and insert existing row data and then move
    > down and repeat for all rows?
    >
    > Example needed:
    >
    > Rows existing
    > 1
    > 2
    > 3
    > 4
    >
    > Rows after Macro
    > 1
    > 1
    > 2
    > 2
    > 3
    > 3
    > 4
    > 4
    >
    > Thanks




  4. #4
    dmexcel
    Guest

    Re: Need Simple Copy and Insert Row, Move Down Macro

    that's great,
    this code is not option explict, is it??


  5. #5

    Re: Need Simple Copy and Insert Row, Move Down Macro

    Thanks for the help, I tried to figure it by looking at recorded code as
    well but couldn't figure past doing it all manually.

    Your code does as shown but I need to get a diff result. I need to copy
    the existing row of data intact directly below where I started.
    Basically inserting a copied row and then move down and repeat next
    single number

    I tried yours out and it wipes out the rows in between.

    my ends up being
    1 1
    2 2
    3 3
    4 4
    5 5
    6 1
    7 1
    8 2
    9 2
    10 3
    3

    I need to end up with exactly
    1
    1
    2
    2

    from the very first line with each pair being duplicate exact

    If you have time to pass some more, thanks, I'll play with your code
    myself and use it to learn the results of the syntax line by line,
    always figured out more that way than trying to lookup in VBA help.

  6. #6
    dmexcel
    Guest

    Re: Need Simple Copy and Insert Row, Move Down Macro

    oh I see
    1
    new row 1
    2
    new row 2


  7. #7
    Tom Ogilvy
    Guest

    Re: Need Simple Copy and Insert Row, Move Down Macro

    It was meant to be, but not tested using option explict. There is a typo in
    an declaration, but not in the executable statements.

    Sub AddRows()
    Dim lastrow As Long, i As Long
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = lastrow To 1 Step -1
    Rows(i + 1).Insert
    Cells(i + 1, 1).Value = Cells(i, 1).Value
    Next
    End Sub


    --
    Regards,
    Tom Ogilvy

    "dmexcel" <[email protected]> wrote in message
    news:[email protected]...
    > that's great,
    > this code is not option explict, is it??
    >




  8. #8

    Re: Need Simple Copy and Insert Row, Move Down Macro

    dmexcel wrote:
    > that's great,
    > this code is not option explict, is it??
    >

    Sorry, I don't know what that term even means. All the Excel I've ever
    learned has been trial and error and help files.

    From both yourself and Tom Ogilvy, the code appears to work on the
    single column, Tom's got me exactly the 1 1 2 2 3 3 sequence, but it
    isn't carrying the rest of the row data with it.

    Sorry If I needed to specify, the rows have several columns of filled
    data in them.

    Tom's ended up giving me
    1 A A A A
    1
    2 B B B B
    2
    3 C C C C
    3

    It doesn't seem to copy the entire row data to produce
    1 A A A A
    1 A A A A
    2 B B B B
    2 B B B B
    etc


  9. #9
    dmexcel
    Guest

    Re: Need Simple Copy and Insert Row, Move Down Macro

    Sub CopyNewRow()
    '
    ' CopyNewRow Macro
    ' Macro recorded 2/23/2006 by Dave
    '

    '
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown
    Range("A1").Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(2, 0).Rows("1:1").EntireRow.Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown
    ActiveCell.Offset(-1, 0).Range("A1").Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(2, 0).Rows("1:1").EntireRow.Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown
    ActiveCell.Offset(-1, 0).Range("A1").Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(2, 0).Rows("1:1").EntireRow.Select
    Application.CutCopyMode = False
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown
    ActiveCell.Offset(-1, 0).Range("A1").Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(-7, 1).Range("A1").Select
    Application.CutCopyMode = False
    End Sub


  10. #10
    Tom Ogilvy
    Guest

    Re: Need Simple Copy and Insert Row, Move Down Macro

    Yes, you should say what you mean. You said rows existing and then showed a
    single column of numbers. Here is an adjustment.

    Sub AddRows()
    Dim lastrow As Long, i As Long
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = lastrow To 1 Step -1
    Rows(i + 1).Insert
    Rows(i + 1).FillDown
    Next
    End Sub

    --
    Regards,
    Tom Ogilvy


    <[email protected]> wrote in message
    news:[email protected]...
    > dmexcel wrote:
    > > that's great,
    > > this code is not option explict, is it??
    > >

    > Sorry, I don't know what that term even means. All the Excel I've ever
    > learned has been trial and error and help files.
    >
    > From both yourself and Tom Ogilvy, the code appears to work on the
    > single column, Tom's got me exactly the 1 1 2 2 3 3 sequence, but it
    > isn't carrying the rest of the row data with it.
    >
    > Sorry If I needed to specify, the rows have several columns of filled
    > data in them.
    >
    > Tom's ended up giving me
    > 1 A A A A
    > 1
    > 2 B B B B
    > 2
    > 3 C C C C
    > 3
    >
    > It doesn't seem to copy the entire row data to produce
    > 1 A A A A
    > 1 A A A A
    > 2 B B B B
    > 2 B B B B
    > etc
    >




  11. #11
    dmexcel
    Guest

    Re: Need Simple Copy and Insert Row, Move Down Macro

    Works for me


  12. #12

    Re: Need Simple Copy and Insert Row, Move Down Macro

    Tom Ogilvy wrote:
    > Yes, you should say what you mean. You said rows existing and then showed a
    > single column of numbers. Here is an adjustment.
    >
    > Sub AddRows()
    > Dim lastrow As Long, i As Long
    > lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    > For i = lastrow To 1 Step -1
    > Rows(i + 1).Insert
    > Rows(i + 1).FillDown
    > Next
    > End Sub
    >


    Sorry, Apologize for that,

    Yes that last does the trick. I did try tweaking it, wondering if just
    changing the

    Cells(i + 1, 1).Value = Cells(i, 1).Value
    to
    Row(i + 1, 1).Value = Row(i, 1).Value

    was the answer, but I never would have got it to continue without your
    code knowledge. I usually will try to trial and error it till I learn
    it but sooner or later I guess I need to get some real VBA training.

    Thanks much for all the help, gonna save me hours of work tomorrow

    Cheers

  13. #13
    Tom Ogilvy
    Guest

    Re: Need Simple Copy and Insert Row, Move Down Macro

    your change would have been

    Row(i + 1).Value = Row(i).Value

    That would work if you know before hand that all the values are constants
    and not formulas or you want to change any formulas to constants.
    Unfortunately, I don't know that. So I used FillDown
    --
    Regards,
    Tom Ogilvy

    <[email protected]> wrote in message
    news:[email protected]...
    > Tom Ogilvy wrote:
    > > Yes, you should say what you mean. You said rows existing and then

    showed a
    > > single column of numbers. Here is an adjustment.
    > >
    > > Sub AddRows()
    > > Dim lastrow As Long, i As Long
    > > lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    > > For i = lastrow To 1 Step -1
    > > Rows(i + 1).Insert
    > > Rows(i + 1).FillDown
    > > Next
    > > End Sub
    > >

    >
    > Sorry, Apologize for that,
    >
    > Yes that last does the trick. I did try tweaking it, wondering if just
    > changing the
    >
    > Cells(i + 1, 1).Value = Cells(i, 1).Value
    > to
    > Row(i + 1, 1).Value = Row(i, 1).Value
    >
    > was the answer, but I never would have got it to continue without your
    > code knowledge. I usually will try to trial and error it till I learn
    > it but sooner or later I guess I need to get some real VBA training.
    >
    > Thanks much for all the help, gonna save me hours of work tomorrow
    >
    > Cheers




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1