VBA copy formulas without the clipboard and get updated relative references?

[SoftDad]

OK, I've got it that I can directly copy a row of formulas without using Select/Copy/Paste -
With range1 and range2 each set to a row:

 range2.formula = range1.formula

This is clearly more efficient and cleaner that using the clipboard.
Problem is, the formulas are copied verbatim, with relative references not getting updated. I.e. if I have

A1 is 2 
B1 is =A1 * A1

and I use the direct copy method to copy this to row 2, I get

A2 is 2
B2 is =A1 * A1

I would expect this to behave as when I copy/paste, i.e I would expect

B2 to be =A2 * A2

Is there a way to get relative address updating in a direct copy? The best I've come up with is to modify my formulas using
INDEX(Sht, ROW(), COLUMN())
to refer to the cell itself (where Sht is a range referring to the whole sheet). Then I use OFFSET to get relative references. My B1 now becomes:

=OFFSET(INDEX(Sht, ROW(), COLUMN()),0,-1) * OFFSET(INDEX(Sht, ROW(), COLUMN()),0,-1)

And when I use my direct copy I get the expected result. But for any by the simplest of formulas this becomes really messy.

The other disadvantage is that if I decide to insert a new column or move my columns around I have to update all my offsets manually.

There must be a better way! Anybody, please!

 

[Eric W]

Have you tried

Range1.Copy Range2

This does use the clipboard, but the dynamic references are automatically changed.

 

[SoftDad]

Have you tried

Range1.Copy Range2

This does use the clipboard, but the dynamic references are automatically changed.

Well hey, I tried your tip and it just works. And Lo! - it doesn't seem to leave junk in the clipboard (which is what has concerned me). It seems to clear the clipboard.
Thanks for that tip, but boy it sure makes me feel dumb after all that formula finagling.

 

[Eric W]

You're welcome. And I can't count how many times I've done things the long way, only to find an easier way later!