Copy formula with relative cell references

[dmkitz]

Sorry to be so helpless -- I am NOT a programmer. A programmer wannabe, maybe. In Excel 2000. I need a macro that will:
1) insert a row above the current selection (I can do that)
2) Copy the formula, from above, down into the cell of the newly inserted row -- that's what I need help with.

I used:

 
    Selection.Copy
    Selection.EntireRow.Insert
    Selection.Offset(0, 1).Select
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

The cell references don't change like they would if you just grabbed the cell handle and copied down on the actual sheet itself.
Thank you.

[Bob Phillips]

With Selection.EntireRow
   .Copy
   .Insert
   .PasteSpecial Paste:=xlFormulas
    End With

[dmkitz]

Thanks, but it pasted the same cell references into the new cell that it copied from the copied cell. If the copied cell refers to A1, I need the reference in the pasted cell to be B1. There must be a way?

This is the code I need to copy:

=IF('[FDS REWORK IP.xls]Financial Statement'!A160="","",'[FDS REWORK IP.xls]
Financial Statement'!A160&" "&'[FDS REWORK IP.xls]Financial Statement'!D160)

[lucas]

Bob gave you a solution....you must have missed something because it works for me.

click on or (select) one of the cells in row 1....hit the button...check the formula in cell c2 and compare it to c1.

see attached with Bobs code

[dmkitz]

This is very strange. The difference is that the formula in my sheet is calling values from a cell in a different sheet. When I used the code to copy a row, the cells that had simple b1*b2 formulas copied correctly but the cells with formulas that were taking values from the other spreadsheet maintained the same sheet and cell reference. HOWEVER, when I just tried it again at home, on Excel 02, the code worked as expected (not on my actual spreadsheet but on a quick mock-up). They don't pay me enough to figure this out over the weekend, so I'll have to take it up again on Monday, but is that at all logical? Thanks.

[RobertBC]

may i add something on it...
maybe you should remember how the cell references works?
remember that there are 4 cell references that will work different when you copy a formula from one cell to another..

just reminding... hope it will help!