cutting and pasting whole tab from one document to another

[buyblue]

I have a giant 2mb excel spreadsheet with macros, special formatting
and formulas galore to help us create quotes for customers. In order to
email this quote to a customer I have to hide all the other tabs (if I
delete them all the formulas go away on the quote tab). What I would
like to do is just send a stripped down version of the quote. I've
tried to cut and paste the quote tab into another spreadsheet but it
either loses all it's values or loses all its formatting. Does anyone
know how I can keep all the information and also delete all the
unnessary information in the document?

 

[Anne Troy]

Hi, Blue. You'll want to get yourself some code that:
a) copies to a new workbook
b) copies all formulas, and pastes special as values
Please don't send your private and VBA-laden workbooks. They CAN be cracked!
Any day now, you'll see a procedure appear here called Formula Zapper:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=698
It's created by Aaron Blood. You won't have access to the page until that
article has been approved, but like I said, should only be a day or so.
Between that, and Ron de Bruin's "email a worksheet", you should have
yourself sending quotes as an email message (instead of as a workbook) in no
time.
************
Anne Troy
www.OfficeArticles.com

 

[buyblue]

So there is no easy way to cut down a quote into one tab and keep all
the formatting and formulas intact? I'm very un-computer savvy. I
wouldn't even begin to know how to create a reference. My limit is with
cut and paste. Can someone please give me a step by step way to do
this? Thanks.

 

[Dodo]

I have a giant 2mb excel spreadsheet with macros, special formatting
and formulas galore to help us create quotes for customers. In order to
email this quote to a customer I have to hide all the other tabs

Wouldn't a printed version do?
Like you get when printing to PDF and E-mailing that.

 

[Dave Peterson]

You could try this:
Open your workbook
start another workbook

Back to the Quote worksheet in your original workbook.
select all the cells (ctrl-a (twice in xl2003) will do this)
Edit|copy

To a new worksheet in that new workbook
edit|paste special|values
followed by
edit|paste special|Formats

Save that new workbook.

ps. you should be aware that if you hide sheets and still send the original
workbook, it won't take much for the recipients to view those hidden
sheets--even if you protect worksheets and protect the workbook.

 

[buyblue]

Dave, there isn't a way to paste special formulas and special formats
at the same time. When I do what you say I end up with a nothing
presentable. I'm using Excel 2000 by the way if that helps. The reason
I need to do this is because of the security issue. I can't believe
that Microsoft is so short-sighted as to not create a simple function
to send a sheet. Unbelieveable.

 

[Dave Peterson]

You mean paste special|values and formats at the same time?

Not that I know.

But I'm not sure why you end up with something not presentable. In what way?

 

[buyblue]

the typing which is very small in the original ends up being gargatuan
130 font and the column formatting is all off.

 

[Dave Peterson]

I don't have a clue why the font changes, but if you're running xl2k or higher,
you could follow up with:

Edit|paste special|column widths

or you could try this:

rightclick on the worksheet tab
select move or copy
move it to a new workbook
then do the select all
edit|copy
edit|paste special|values

Remember to close the original workbook without saving!

Do you have any code under that worksheet--like for worksheet_change or
_selectionchange for a control from the control toolbox toolbar?

If you do, you'll have to remove that code (if you want).

Debra Dalgleish has some instructions with pictures:
http://www.contextures.com/xlfaqMac.html#NoMacros

That'll help you do that.

 

[buyblue]

Dave, thanks for your help. I wasn't able to copy the tab and move it,
but for some reason WAS able to drag the tab to a separate worksheet
and it retained all the formulas and formatting. Weird huh?

 

[David McRitchie]

there is a check box at the bottom of the move or copy dialog
to create a copy.

 

[Dave Peterson]

And if you're dragging and dropping, remember to hold the ctrl-key when you
click the worksheet tab.

 

[buyblue]

why do I need to hold the ctrl key? It works just fine if I don't.

 

[Dave Peterson]

If you click and drag, it does a move.

If you ctrl-click and drag, it'll be doing a copy.

(depends on what you want to do.)