pasting data from one sheet to another with formatting and cell references

**excelname** · 02-11-2010, 02:35 PM

I have inherited a monstrous speadsheet on which all data appears on a single sheet rather than being broken up across several sheets in some logical fashion.

One portion of this monster is a set of quite a few form letters, each of which reference various cells elsewhere in the spreadsheet.

I want to move all of these letters, together, to another tabbed sheet as a first step in reorganizing the monster.

Simply copying and pasting the cells or columns doesn't work. It fails in different ways depending on which pasting options I employ.

A very simplified version of my problem appears on the 5 sheets of the attached file, with what I hope is just enough further detail about the difficulty.

By the way, I also tried using the "Insert Copied Cells" option when pasting but since this failed with the exact same results as one of the other options I didn't include the results in the example shreadsheet.

I am grateful to anyone who will take a look at this problem.

**JBeaucaire** · 02-11-2010, 02:56 PM

1) Go to sheet 1 and highlight the boxed area
2) Press CTRL-X to cut cells
3) Click on the target in the next sheet and press ENTER

**excelname** · 02-11-2010, 04:12 PM

Originally Posted by JBeaucaire

1) Go to sheet 1 and highlight the boxed area
2) Press CTRL-X to cut cells
3) Click on the target in the next sheet and press ENTER

So the trick seems to be to Cut and Paste rather than Copy and paste.

Nice.

Thanks.

This does not in itself bring over the customized column widths, though.

In case anyone is interested, to get it done with the original columns widths in the desired columns of the target sheet you can follow these steps:

- Highlight and copy the relevant columns in source sheet (so don't highlight cells in the sheet but rather the A,B,C,D,ETC heading "cells" across the top.)

- Choose where you want similar columns to appear in the target sheet, click the leftmost one of those and Paste.

- This adjusts the column widths but puts impaired data in the taret cells and may put in lots of extraneous data above and below the zone you're concerned with.

- Since the cells are still highlighted, just hit delete to clear them. In my case I then also deleted the box which still appeared after the delete command. You might also need to separately delete background colors, etc. (maybe there is a single stroke to delete all this; not sure)

-Then proceed with the beautifully simple steps from JBeaucaire's solution above.

Thanks, JBeaucaire.

**excelname** · 02-11-2010, 04:15 PM

Sorry, JBeaucaire. I tried to contribute to your reputation again, since you solved another problem for me, but the site insists that I must credit someone else first even though no one else has even responded.

Pity.