Making An Exact Copy Of A Range Of Formulas

Category: General / Formulas / General VBA | [Item URL]

Assume that A1:D10 on Sheet1 has a range of cells that contain formulas. Furthermore, assume that you want to make an exact copy of these formulas, beginning in cell A11 on Sheet1.  By "exact," I mean a perfect replica -- the original cell references should not change.

If the formulas contain only absolute cell references, it's a piece of cake. Just use the standard copy/paste commands. But if the formulas contain relative or mixed references, the standard copy/paste technique won't work because the relative and mixed references will be adjusted when the range is pasted.

If you're a VBA programmer, you can simply execute the following code:

With Sheets("Sheet1")
 .Range("A11:D20").Formula = .Range("A1:D10").Formula
End With

Following are step-by-step instructions to accomplish this task without using VBA (contributed by Bob Umlas):

1. Select the source range (A1:D10 in this example).
2. Group the source sheet with another empty sheet (say Sheet2). To do this, press Ctrl while you click the sheet tab for Sheet2
3. Select Edit - Fill - Across worksheets (choose the All option in the dialog box).
4. Ungroup the sheets (click the sheet tab for Sheet2)
5. In Sheet2, the copied range will be selected. Choose Edit - Cut.
6. Activate cell A11 (in Sheet2) and press Enter to paste the cut cells. A11.D20 will be selected.
7. Re-group the sheets. Press Ctl and click the sheet tab for Sheet1
8. Once again, use Edit - Fill - Across worksheets.
9. Activate Sheet1, and you'll find that A11:D20 contains an exact replica of the formulas in A1:D10.

Note: For another method of performing this task, see Making An Exact Copy Of A Range Of Formulas, Take 2.