You could use an event to do that. Copy the code below, right-click on the sheet tab, select "View
Code", and paste the code in the window that appears. You will need to set the address of 1A (the
"$B$2") in the macro, as well as the column where the formulas are found: change the myCol = 3 to
the number of that column.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim newVal As Variant
Dim myRange As Range
Dim myCol As Integer

myCol = 3

If Target.Address < "$B$2" Then Exit Sub
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
Set myRange = Cells(Rows.Count, myCol).End(xlUp).EntireRow
myRange.Copy
myRange.Insert xlDown
With myRange.Offset(-1, 0).EntireRow
.Copy
.PasteSpecial xlPasteValues
End With
Target.Value = newVal
Target.Select
With Application
.EnableEvents = True
.CutCopyMode = False
End With

End Sub

"rbane" wrote in message
...
Here is my situation:

My spreadsheet has one row per day. I add an additional row every day.
The values in each cell of the rows is the product of two other cells (call
them 1A and 1B) that are located elsewhere on the spreadsheet.
Each day I change the value of 1A and that causes the values in the cells
in the rows to change accordingly.
Each day before I change 1A however, I special paste all of the rows so that
they do not change.
I have set up a macro to do all that and that is working fine. However, I
would like the macro to also automatically add another row for todays date so
all I have to do is enter the value of 1A and the new row will reflect todays
values.
I hope that is clear and I really appreciate anyones help.






--
rbane


"Bernie Deitrick" wrote:

rbane,

What's the problem? Post your code along with a description...

Bernie
MS Excel MVP


"rbane" wrote in message
...
I thought it was working fine, but I have a problem with it that I need some
help with. I'd appreciate any help anyone would be willing to give me.

Thank you.
--
rbane


"rbane" wrote:

My macro is working fine, thanks to your suggestion.
--
rbane


"Bernie Deitrick" wrote:

Ron,

Before changing A1, copy B1 and pastespecial it as a value over the formula
in B1.

HTH,
Bernie
MS Excel MVP


"rbane" wrote in message
...
Can anyone please help me with this problem ?

Assume I have a row of cells named B1 and B2.
Today I want the value in B1 to be the product of A1 times A2.
Tomorrow, I will create another row of cells named C1 and C2. I will
change
the value of A1 and I want the value in C1 to be the product of the new A1
times A2.
I do not want the old value in B1 to change from what it is today.
Can you help me with this?

Thank you very much,

Ron



--
rbane