Copy & Paste values & formatting

[Dave Rey]

Hi

I have a huge workbook which contains many sheets - all
linked by formulas to other sheets.

When I distribute this file to people at work, I need to
remove all the backing files and just present the final
data - so I have to open a new file and copy & paste
values & formats, then delete the backing sheets. A lot
of the formatting in each sheet involves merged cells.

When I try to do this worksheet at a time, and try to
paste values, it gives me an error message "this operation
requires the merged cells to be identically sized" - even
if the cells are in fact identically sized. This means I
cannot do the process by copying sheets at a time, and
have to do it on specific cell ranges.

What I am after is a simple way to take a (say) 30
worksheet Excel file, and copy and paste the formats and
values of say, 20 of the sheets, without havig to go into
the sheets individually and manually copy and paste values
for specific areas.

Thanks again for your help in advance

Dave Rey

 

[Pat Jennings]

How are you distributing the pages? If can easily publish the sheets as Web
pages with or without underlying calculation capability. Click on FILE,
SAVE AS WEBPAGE and then follow the instructions.
If you are actually distributing the pages in paper format, this may ease
that process as well.

 

[Dave Rey]

The files are being distributed by email - mainly to
directors who will not be able to cope with anything other
than a normal Excel file. Thats why I need the capability
to present the data in formatted value-only style.

Dave

 

[Pat Jennings]

Then, look at my prior message. It should solve your problem rather easily.

 

[Dave Rey]

Publishing as a web page is not suitabel I'm afraid. For
a start people want to receive an Excel spreadsheet and
would be phased by a htm file. Also, once it is copied
and pasted as values, I perform some editing work before I
send it to the various people.

I need the file to be an Excel xls spreadsheet, but just
with all the formulas removed and replaced by the values
from the original version.

Thanks

Dave

 

[rbanks]

I do this all of the time! Take your current file xxx.xls and rename i
xxx_formulas.xls. Update links as normal, then save.

Then, do a save as to the original name xxx.xls.

Highlight all sheets at once, click the box at the top (highlight
entire sheet), and copy - paste - special -values right over itself.

Now you have the entire workbook as values! From there, you can delet
or hide the source sheets that you don't want your audience to see. I
any links are remaining, the find links wizard should be able to wip
them out for you.

Email out this file, and next time you update, start over with th
"formulas" file.

Let me know if this isn't clear

 

[Dave Rey]

Brilliant - works perfectly.

I Can't believe it was so straightforward. It will save
me an awful lot of time each month now I know that!

Thanks again

Dave