How to copy formula across a range without starting at original reference?

[doge_3]

Let's say I have some formulas pasted in cells A1 through D1.

I have a col E that has names starting from E5:E20

I'm using the following code right now to paste the formulas from A1:D1 at A5:D20 like this:

With Sheets("Sheet1")
        .Range("A5:C" & .Cells(.Rows.Count, "E").End(xlUp).Row).Formula = .Range("A2:C2").Formula        
End With

However, at A5, it starts by referencing the things from the first row. I want it to start referencing from the 5th row.

For example, if B1 has the formula =A1*5, my current code pastes into B5 the same thing as B1. I want it to instead use the formula =A5*5.

How can I change the code to do that?

 

[Jerry Sullivan]

Your description references Row 1 as the row holding the source formulas and the code example references Row 2.
Assuming it's Row 2 here's one way....

 With Sheets("Sheet1")
   .Range("A2:C2").Copy
   .Range("A5:C" & .Cells(.Rows.Count, "E").End(xlUp).Row).PasteSpecial (xlPasteFormulas)
 End With