Thread: Add line and copy formulas doesn't work with a macro

Hi everybody,
I'm trying to create a macro that adds and copy formulas into a new line from an existing one (in this case, row 2)
The steps i'm going through are :
1. go to function "the line to copy from"
2. Insert row
3.Name the new inserted row (or concerned cells)
4.Go to "The line to copy from" agains
5. Paste
6. Copy or Copy formulas
7.Goto the line i've just created
8. Delete its name using insert-name-delete

The thing is when i do this manually 3 or 4 times, it works but the macro only works once. On the second trial, the line is created but nothing is copied frome the baseline row.

Check the attached file,

Do you have an idea why it doesn' work ? Another way of achieving the same result ?

Thanks a lot in advance,

JohN

To edit your code you could simply change your "COPYTO" line to:

ActiveWorkbook.Names.Add Name:="COPYTO", RefersToR1C1:=Selection

Alternatively, you can avoid using "select, copy, and paste" altogether, as below:

Sub COPYTO()
    Application.ScreenUpdating = False
    Range("COPYFROM").EntireRow.Insert
    Range("COPYFROM").Offset(-1, 0).FormulaR1C1 = Range("COPYFROM").FormulaR1C1
    Application.ScreenUpdating = True
End Sub