Copy formulae to range without Copy/Paste

I want to copy the formulae in cells A1:D1 to a range A10:D20 without using copy/paste.
The result will be to repeat the formulae in the first 4 cells of row 1 into the first 4 cells of rows 10 to 20 in a relative manner.
I am aware of the concept for copying values without copy/paste, but substituting .formula for .value does not work.
It may be simple, but so far it has eluded me!

Thanks
A

Thanks TMS,

Maybe I have not explained myself well.
This is a hypothetical situation.
In exploring the VBA functions it is clear that using the clipboard to copy and paste is very inefficient.

eg:

if the formula in A1 = 1+A2 , B1 = 2+B2, C1 = 3+C2, D1 = 4+D2,

Range("A1:D1").Copy
Range(Cells(10, 1), Cells(20, 4)).Select
ActiveSheet.Paste

will copy all cell attributes of the first range into the second range, and the results are correct,but takes 2 seconds to run.

Using the range method,

Range(Cells(10, 1), Cells(20, 4)).Value = Range("A1:D1").Value

will instantly duplicate the values of A1 to D1 into the larger range and, as expected, all the values in a column are the same.

Trying to use a similar function for replicating the formula (relative, just as in the copy/paste method)
eg

Range(Cells(10, 1), Cells(20, 4)).Formula = Range("A1:D1").Formula

does NOT give a relative result: the formula in A10 becomes 1+A2 whereas it should be 1+A10
(FormulaR1C1 and FormulaLocal are no better)

So I am looking for a Formula method similar to the Value method.

Suggestions appreciated!

A

Thanks TMS,

I am running these tests on a blank worksheet, so there should not be any spurious effect going on affecting run time.

Your Test2 gives the right values result and takes 1 second whereas Test1 (copy/Paste) takes 2 seconds.
So that's a good improvement.

Your Test3 gives an instant result with the right values (but does not reproduce the formula),

Omitting the "Value" line from Test3 gives an instant result with the relative formulae as required.
So in principle that's the solution I am looking for, but it has a preconception of the simple cell formula in my example.

However, the formulae I will ultimately be running will be different in each starting cell and are much more complex,
so I need to turn this into a generic solution which simply reproduces whatever formula is in the starting cells.

Could it be that I need simply to replace "+A11" in your formula with any formula as in the starting cell?
And as each starting cell has a different formula perhaps I should to run it separately for each?

A

Thanks TMS

Sorry for delay in response (called away)

I have no other workbooks open, and am using just the one test sheet.

Using your code I get the same times as previously.

For comparison I have tried it on my laptop and the results are better, 1 sec instead of 2 sec. for copy/paste.

As there is apparently no macro capable of reproducing the copy/paste function without using the clipboard, I have a workaround.

Range("A1:A20").Formula =Range("A1").Formula
etc for each column, then clear cells A2 to D9.

This works instantly and gives the correct results.

Many thanks for your time and effort.

A

Postscript

I have noticed that there is no delay when using copy/paste on text (rather than formulae).

So if any formulae in cells A2:D9 need to be retained rather than cleared, they can be converted to text (using CtrlH, replacing = with #) and copy/pasted to a blank area before running
Range("A1:A20").Formula =Range("A1").Formula
for each column. Then the text cells can be copy/pasted back to A2:D9 and refomatted to formulae (using CtrlH, replacing # with =).

This runs instantly, and the result is correct.

A