Table - New Rows with automatically duplicated their columns values/formulas


New Member
Aug 20, 2014
Hi, what I basically want to achieve is the following scenario:

[B]A1 - TABLE HEADING      [/B]O1
A2 - 1 2 3            ="A2"
A3 - 4 5 6            ="A3"

When I insert new values into a table, the new row is created. I would like Excel to automatically duplicate also the other columns (not only those from the table),
like O2 and O3, so that I do not have to always press CTRL and copy them down all the time when I create a new record in the table.

Any ideas?

Thank you for your help in advance.
Last edited:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
You'll need VBA to do that.
If you want to go that way you'll need to provide what cells in the row are going to be manually populated !
Upvote 0
@Michael M - Thank you for your reply.

I would like to populate cells in column (O), where there is the text: "POPULATED F.".

Here is the picture for better understanding of what I would like to achieve.

Thank you for your reply and have a nice day.
Upvote 0
I can't access images.....Have a look at my tag for the HTML Maker.
Use that to post a sample of your data.
Upvote 0
@Michael M

I cannot download any files now.

Everything is shown in the picture.
I think that the screenshot serves best for this kind of a problem.

    A (column)                    B (column)                        C (column)
1   Table heading                 Table heading No. 1               formula
2   Table row No. 1               apple                             formula
3   Table row No. 2               banana                            formula

I would basically want column "C" to be automatically populated when there is a new row generated by table. When you enter any values into a table, then a new row is created (the styles will be applied to that row and so).

Once I enter values, I have to select the last row from column "C" and copy the value (in this case, it's a formula that's copied) by holding a ctrl button, one row down.

Is there any possibility using VBA to automatically duplicate the formula (with the right linking) when there is created a new row in a table?

The table looks like:
Weight              Fruit               Formulas
10 lbs              apples              apples - 10 lbs
20 lbs              bananas             bananas - 20 lbs

I hope that this explanation should be enough for anyone who is willing to help me. Also I have attached a picture...

Thank you again and have a nice day.
Last edited:
Upvote 0
I have tried the following code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim lastRow As Long
    lastRow = Range("D" & Rows.Count).End(xlUp).Row
    Range("O4").AutoFill Destination:=Range("O4:O" & lastRow)
End Sub

But if I were about to use this approach, I'd lose the history (undo and redo delete function), which is what I do not want.
Moreover, there is also some flickering when using this code. Do you have any better ideas?

Thank you.
Upvote 0
@shg - First, thank you for your reply.

I wanted there to be some space between the table and column C.
Better solution for me would be not to even see it.
Yesterday, I realised (independently on your approach) that the column C could be part of the table without using the VBA. Eventually I could hide the column C.

I like your approach and there is no need to use the VBA to do the job.. I thought it :)

Thank you again, shg!
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back