Making An Exact Copy Of A Range Of Formulas
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.
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks