copying values/text, not formulas

  • Thread starter Thread starter snoopy369
  • Start date Start date
S

snoopy369

Heya ... I've got a workbook that consists of a "main page" with several
columns of formatted text, and several columns of VLOOKUP'ed text drawn
from several other worksheets in the workbook. My boss wants only the
main page and not the others (too much space on his HD) ... I've been
trying to use "Paste Special" to do this, first copying the formats
over to a new page (looks exactly like what I have on the main page)
and then copying the values.

It works fine as long as I have only single cells selected. What goes
wrong is when I have merged cells selected. Apparently "Paste Special
- Values" is very very picky. Even when I have exactly identical (as
in pasted via PS-Format) merged cells, it still gives me the error
message "This requires merged cells to be identically sized" ... even
when it's a simple two cell merging.

Is there any other way to do this? I've got a decent store of VB
knowledge now, and wasn't sure if something like Cell.Values would work
(since it's text ... the Value() function does NOT work).
Cell("contents",A1) or whatever doesn't work when I re-open the
spreadsheet somewhere where the original A1 doesn't exist, hence why I
want to copy only values. And it's not an option to remake my
spreadsheet without the merged cells (although if I could create an
intermediate spreadsheet w/o merged cells and then re-do it on the
final product that would be OK, but that seems at minimum horribly
complicated) ...

Thanks in advance! If you're doing this on email please email
snoopy369 at yahoo dt com w/reply :)

-Joe
 
snoopy369 > said:
Heya ... I've got a workbook that consists of a "main page" with several
columns of formatted text, and several columns of VLOOKUP'ed text drawn
from several other worksheets in the workbook. My boss wants only the
main page and not the others (too much space on his HD) ... I've been
trying to use "Paste Special" to do this, first copying the formats
over to a new page (looks exactly like what I have on the main page)
and then copying the values.

It works fine as long as I have only single cells selected. What goes
wrong is when I have merged cells selected. Apparently "Paste Special
- Values" is very very picky. Even when I have exactly identical (as
in pasted via PS-Format) merged cells, it still gives me the error
message "This requires merged cells to be identically sized" ... even
when it's a simple two cell merging.

Is there any other way to do this? I've got a decent store of VB
knowledge now, and wasn't sure if something like Cell.Values would work
(since it's text ... the Value() function does NOT work).
Cell("contents",A1) or whatever doesn't work when I re-open the
spreadsheet somewhere where the original A1 doesn't exist, hence why I
want to copy only values. And it's not an option to remake my
spreadsheet without the merged cells (although if I could create an
intermediate spreadsheet w/o merged cells and then re-do it on the
final product that would be OK, but that seems at minimum horribly
complicated) ...

Thanks in advance! If you're doing this on email please email
snoopy369 at yahoo dt com w/reply :)

-Joe
Click to expand...

Your problem is more likely to do with having merged cells than with Paste
Special > Values. Merged cells are known to cause problems in many ways.
Could you remove the merging and use instead
Format > Cells > Alignment tab > Horizontal > "Center Across Selection" ?
I have never found this to cause a problem like merging cells does.
 
Hi Snoopy369!

Try cheating!

If you use Ron de Bruin's SendMail, there is an option that allows
sending a single sheet and within that option you can select "Values".

It also has the advantage that you can send it straight to your boss
with the size challenged hard drive.

Ron's SendMail is a free Addin obtained with a User Guide from:

If you're using Outlook:
http://www.rondebruin.nl/sendmail-addin.htm

If not:
http://www.rondebruin.nl/sendmail.htm#Add-in


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Norman:
Thanks for the suggestion, but with me being on an evil corporat
intranet, there's just no way i'd be able or allowed to pull somethin
like that ;) Otherwise that would be fantastic though!

Paul:
I may try playing with that ... the format is fixed, that I have t
follow, but I may be able to cheat with CenterAcrossSelection ... i 'l
see what I can do tomorrow :)

Thanks!

-Jo
 
I was able to work it out by doing a complicated copy and paste
technique -- for those interested, essentially:

(create new sheet, copy of 1st)
(remove merging of cells in copy)
(copy cells from copied sheet, pastespecial - values into 3rd sheet)
(copy cells of 1st sheet, pastespecial-formats into 3rd sheet)

leaving me with a 3rd sheet that's pristine :) I can then move that
into a new workbook at my leisure. :) Email me if anyone in the future
wants the actual code (i'll keep it around for a while).

Would be nice if excel played nicely with Merged Cells, though --
they're used a lot in the corporate world of formatted reports. :)

-Joe
 
Back
Top