Auto copy column formula to the new row

  • I'm working on a spreadsheet wherein I need to auto copy the formula from the above cell to the new cell below which is technically a new row.


    Attached is a snapshot on what I have in excel using the formula in column "H" =SUM(H2,F3,-G3) to calculate the BALANCE column.


    So based on my example, if a new row is inserted in Row 8 the formula in cell H8 should be =SUM(H7,F8,-G8) this should be automatically inserted.


    Is there a way I can do this automatically?


    TIA!

  • Re: Auto copy column formula to the new row


    You could add this code to the worksheet module

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Fr As Long
    If Target.Rows.Count > 1 Then Exit Sub
     Fr = Target.Row
      If Not Range("H" & Fr).HasFormula Then
        Range("H" & Fr).Formula = "=SUM(H" & Fr - 1 & ",F" & Fr & ",-G" & Fr & ")"
      End If
    End Sub


    When you insert a new row it fires this change event and will fill the cell with the required formula. Without knowing any more about your worksheet and data, I cannot speculate on how this will totally interact with it. Anytime a cell on the sheet changes it will look for cell in column H of the target row for a formula. If there is not already a formula in this cell, it will put it there. If there are areas of the worksheet with activity that you do not want this to happen then please provide more info and the code can be modified to suit.

  • Re: Auto copy column formula to the new row


    Hi EggCel... thank you for creating the code. I tried to insert it in my worksheet module and the formula is automatically inserted now in the H column of the new row. However, the formula in the H column below the new row is not updated. Everything below the new row should be incremented by 1 to make the computation correct.


    example:
    new row inserted in row 18 - H column shows =SUM(H17,F18,-G18) >> this is correct :)
    in row 19 - H column still shows =SUM(H17,F18,-G18) >> this should be now =SUM(H18,F19,-G19) and so on until the last row with data.


    I hope this can be done to complete my goal..


    thanks for your help

  • Re: Auto copy column formula to the new row


    After some brief reading on inserting rows with formulas, try this:

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim Fr As Long
        If Target.Rows.Count > 1 Then Exit Sub
        Fr = Target.Row
        If Not Range("H" & Fr).HasFormula Then
            Range("H" & Fr).Formula = "=SUM(H" & Fr - 1 & ",F" & Fr & ",-G" & Fr & ")"
            Range("H" & Fr + 1).Formula = "=SUM(H" & Fr & ",F" & Fr + 1 & ",-G" & Fr + 1 & ")"
        End If
    End Sub


    it will update the formula in the new row as well as the code in the row that was displaced.

  • Re: Auto copy column formula to the new row


    Thanks EggCell! The code worked perfectly when i do manually insert a new row. However, this is not the way how the new rows are inserted. The new rows are inserted from an external data which is an MS Access query. (I guess I should have mentioned this from the start)


    If I run the import data, the rows are inserted but the formulas in column H are not populated :( because the import from ms access query sometimes imports multiple rows maybe the code doesn't cope up with the activity.


    I'm now thinking if a there is a code that will do a "copy cell" say starting from H10 upto the last row that has data in it..


    All I want is just calculate the H column automatically when a new row/s is inserted from an MS access query.

  • Re: Auto copy column formula to the new row


    michibahn


    Please do not quote entire posts. When quoting follow these guidelines


    1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
    2. Quote ONLY the specific part of the post that is relevant - - not the entire post.


    This will keep thread clutter to a minimum and make the discussion easier to follow.
    Thanks

  • Re: Auto copy column formula to the new row


    Hi EggCel.. sorry to bother you again. I found something unusual with the code. If only 1 new row is inserted, it works perfectly fine. However, if 2 new rows are inserted, the 2nd and 3rd rows from the last row doesn't have formula in Column H. If 3 new rows are inserted, 2nd, 3rd, and 4th rows from the last row doesn't have formula.


    So to summarize, if 2 new rows is inserted, I will get 2 blank cells in colum H starting from the 2nd row from the last row and up. If 3 new rows inserted, I will get 3 blank cells in column H starting also from 2nd row from the last row and up. This scenario is encountered on the number of new rows inserted in the table.


    Attached is the snapshot of what I'm trying to explain.


    Can there be any adjustment in the code? I really appreciate your help.


    TIA!

  • Re: Auto copy column formula to the new row


    Try this


    It should work depending on how you are inserting the multiple rows. I tested it manually and by macro and it worked both ways.

  • Re: Auto copy column formula to the new row


    Hi EggCel.. code seems to do its job. However, can it skip copying formulas startiing row #9 and up and the last row with data? because my Row #9 and above are pure texts and table names and my last row has a grand total which has a separate formula


    And can you teach me how to do this using macro? I can't do much in the table since every bit of changes in the table, it copies formula in column H and cannot even delete a row now it'll just inserts the formula in column H.


    Attached is what my final table looks like..


    Thanks so much for your help :)

  • Re: Auto copy column formula to the new row


    Try this


    If the row number is less than 10 it will do nothing. The only caution is that if you insert rows in rows 1-9 it will not populate with the formula as the old rows move down.

  • Re: Auto copy column formula to the new row


    Hi EggCel.. that did the trick on the row 9 and above. One last, can the code not apply to the very last row of the table with data? because my last row has a different formula and different label "TOTAL" and also if I made a change in the new rows below, I couldn't erase them it'll just enter the formulas in column H since it treats them as a worksheet_change.


    Here's my final table looks like.


    thanks again

  • Re: Auto copy column formula to the new row


    Given the added information I modified the code. Try this instead.


    Will there be anything in column h after this table? If so please explain so that the code can accomodate. Also, if there is any other pertinent details please advise.

  • Re: Auto copy column formula to the new row


    Hi EggCel.. column H from row 10 onwards has only the formulas that needs to be copied except for the very last row whihc is the TOTAL and has a different formula in column H. the table will just go on as the new rows are inserted.


    I think the new code is messed up compared to the last one. When I inserted a new 2 rows, it infinitley copied the formula on column H until row 10074. My goal is to stop copying the formula starting from the very last row which is the "TOTAL" and onwards. By the way, the TOTAL row (very last row) formula in column H is (very last row value in column F - very last row value in column G)


    See the attached snapshot.

  • Re: Auto copy column formula to the new row


    Hi EggCel.. I'm just gonna revoke my reply above. I think the code works perfectly fine. What I did is just delete the pasted formulas until row 10074 and corrected the formula of the TOTAL row and saved the file. Now, whenever a new row is inserted, the formulas in rows 10 onwards (except the very last row) is copied fine in column H and the TOTAL row retained its correct formula in column H.


    I can't thank you enough for creating this code for me :):) thank you so much!

  • Re: Auto copy column formula to the new row


    Hi EggCel and all.. Ok I guess I'm gonna bother you again because of a new requirement. The users of the table now want to group the entries based on a unique code in column A and create a SUB TOTAL per each unique set. I don't know if this is workable. The difficult thing is the the formula in column H is not a continuous copy from the top or below row. Instead if a new group is separated, the next group will start its own calculation and copy the formula only within its group.


    I just don't know if I explained it clearly but I'm attaching the spreadsheet to see how they want it to be.


    Thanks again!

  • Re: Auto copy column formula to the new row


    How are new entries made on the worksheet? This will give a better understanding of how to implement what you are after. Are new unique part numbers added, or is it just that more items are added to existing part number rows? Please provide more specific details.

  • Re: Auto copy column formula to the new row


    Hi EggCel, there will be new unique part numbers added as the table goes on and also more items are added in the same exisiting part number rows. These new entries/itmes will be coming from an MS Access query which is triggered in Excel using the Import External Data option.


    I mean the auto-copy formula code you made is working fine using the same way the entries are made. Its just that they want to groupe them now and create a SUB-TOTAL for each similar entries :( I thought I was done yesterday :(


    Thanks so much EggCel!

  • Re: Auto copy column formula to the new row


    What does the table look like after the query, are the subtotal rows and formats already in place or did you add those. Can you post a before sheet(what it looks like after the query updates), and an after sheet(what the end result should look like.)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!