0

Problem: You want to duplicate a range of cells to a different position on the same worksheet.

Sadly, a regular copy/paste will 'helpfully' fix all references in the formulas so they point to the wrong cells (i.e. the original cells plus an offset).

And if you can't just use static references in the formulas because they are e.g. already an intentional mix of static and relative references, you are out of luck.

So, how can you do it without VBA?

Is there an easy way to copy a whole block 1:1 without changing the formula text?

Aroddo
  • 93
  • 1
  • 5

1 Answers1

1
  1. Select the range you want to duplicate. (position A)
  2. Copy/paste the selection to somewhere else (position B) as backup. References will change, but that's ok.
  3. Again, select the range you want to duplicate. (position A)
  4. MOVE the selection to the target position C with your mouse (click and hold the fat border on the selection.
  5. Copy/cut/paste the backup from position B to the original location (position A)

Now you have an exact copy of the original range with references pointing to where they should.

Confirmed on Excel 2007.

Aroddo
  • 93
  • 1
  • 5