Automatically filling newly created rows

Hey everybody,

I have a large database in excel. Sometimes I will add rows to this database.
I want to reduce my manual labour by making a vba code that will automatically copy the formula of the cell above the newly created empty cells into the new cells. So for instance if cell B1 has the formula "=A1" and I create a new second row, I want that the formula in newly created cell B2 becomes: "=A2"

What I made is the following:

Sub refresh()
Range("BS65536").End(xlUp).Select
Range("BP3", ActiveCell).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
End Sub

My problem now is that, continuing on my example, the cell B2 will have the formula "=B1" instead of "=A2", anyone one knows how to fix this?
Thanks for your help!

Jeroen

Hey!

Thank you for your fast reply and my apologies for my poor English which made you indeed misinterpret my question.

I will try to explain it better.

The excelsheet I have is a customer file. Now of course, sometimes I need to add new customers. These are ordered on surname, so I will need to create a new row to add them in the right order of descending first letter. Some columns of the new row I need to fill in manually (think of name, address, purchases etc) but some others (think of sum of purchases etc) are just some formula's that use data from that particular row.

I now have to copy these formula's into the newly created cells (so for instance again, cell BP4 might be "=SUM(A4:BB4)" every time when I add a new row to the file. I now wish to create a macro that will automatically copy these formulas.

What I therefore did was to make a macro that first selects the range of the columns that include these cells containing these kind of formula's (thus for instance the sum of purchases), find the empty cells in these colums (this will be the cells of the newly added rows) and then somehow copy the formula that was above it into the new cell. So again for instance if the cell above the empty cell (lets say BP4 again) was "=SUM(A4:BB4)", the new cell should be filled with "=SUM(A5:BB5)". However with the macro I have written it will now be filled with "=BP4", which is of course not what I want, because it will give me the sum of purchases of the old customer and not the new one.

I hope this clarifies things, thanks again for your help!