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):
- Select the source range (A1:D10 in this example).
- Group the source sheet with another empty sheet (say Sheet2). To do this, press Ctrl while you click the sheet tab for Sheet2
- Select Edit - Fill - Across worksheets (choose the All option in the dialog box).
- Ungroup the sheets (click the sheet tab for Sheet2)
- In Sheet2, the copied range will be selected. Choose Edit - Cut.
- Activate cell A11 (in Sheet2) and press Enter to paste the cut cells. A11.D20 will be selected.
- Re-group the sheets. Press Ctl and click the sheet tab for Sheet1
- Once again, use Edit - Fill - Across worksheets.
- 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.
Post a Comment