Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Aug 2004
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert Row & Copy Formula (Excel 97)

    When I insert a new row into my spreadsheet, it does not copy the formulas from the cells below. Is there a way to have it copy the formulas and insert a new row at the same time?

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

    Re: Insert Row & Copy Formula (Excel 97)

    You could create a macro for that, but you'd have to specify exactly in which columns the formulas should be copied.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Insert Row & Copy Formula (Excel 97)

    As Hans said, assuming you want to copy all the formulas and allow the references to retain their relative or absolute character:

    Sub CopyInsertRow()
    ActiveCell.EntireRow.Insert Shift:=xlDown
    ActiveCell.EntireRow.Offset(1, 0).Copy ActiveCell.EntireRow
    Application.CutCopyMode = False
    End Sub

    No error handling for locked cells etc.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Insert Row & Copy Formula (Excel 97)

    Does this do what you want:

    Public Sub InsertAndCopyFormulas()
    Dim oCell As Range
    ActiveCell.EntireRow.Insert
    For Each oCell In ActiveCell.Offset(1, 0).EntireRow.SpecialCells(xlCellTypeFormulas)
    oCell.Copy Destination:=oCell.Offset(-1, 0)
    Next oCell
    End Sub
    Legare Coleman

  5. #5
    Star Lounger
    Join Date
    Aug 2004
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Row & Copy Formula (Excel 97)

    I've actually never written a macro before so I don't really know what to do with the code that you wrote. <img src=/S/help.gif border=0 alt=help width=23 height=15>

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

    Re: Insert Row & Copy Formula (Excel 97)

    See Legare's <!post=Personal.xls Tutorial (All),118382>Personal.xls Tutorial (All)<!/post>.

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

    Re: Insert Row & Copy Formula (Excel 97)

    In addition to what Hans said, this particular macro expects the sheet where you want the row inserted to be the active sheet. The macro will insert a row above the row with the active cell and copy the formulas from the row with the active cell to the inserted row.
    Legare Coleman

  8. #8
    Star Lounger
    Join Date
    Aug 2004
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Row & Copy Formula (Excel 97)

    Thanks for all the information and tutorial.

    I have managed to create the macro. However I need to edit it a bit. I actually need it to insert a new row and only copy the formula of one cell (Column C). How do I do that?

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

    Re: Insert Row & Copy Formula (Excel 97)

    The following 2 lines will insert a row where the active cell is, and copy the formula in column C:

    ActiveCell.EntireRow.Insert
    Range("C" & (ActiveCell.Row - 1) & ":C" & ActiveCell.Row).FillDown

  10. #10
    Star Lounger
    Join Date
    Aug 2004
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Row & Copy Formula (Excel 97)

    Thanks so much that worked great!

Posting Permissions

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