Need to extract sheets out of workbook into new file

[Don]

If the formulas have done their work and are no longer
needed, I've done this with VBA in a macro. First save the
sheet(s) you want to email to a new Workbook. Then select
and copy the entire new sheet and paste special, value,
back on top of itself. That clears the formulas (reduces
the size of the file greatly) and enters only the data
that was on the orginal sheet. An email function may also
be added to your macro if you want.

This entire process is handled by a click of one button.

HTH

Don

-----Original Message-----
I have a 25MG soreadsheet that is an order form. I need

the end user to be able to click a button, extract only
sheets with order entry on them and send as a attachment
file in an e-mail.

Or just send the sheets to a new file and then have the end user e-mail it.

The size of this file is so large because of massive use

of formulas. And they will be uable to send the file as is
because of the size.

 

[B Schwarz]

Unable to do this. Some of the End-Users of this spreadsheet do not know much about using computers. They may do data entry in up to 15 or 20 sheets of the workbook(will have complete total of about 140 sheets) and I dont which ones they will choose. I dont know of a user-friendly way to set it up for the sheets the enter data on to be extracted.
A friend was able to come up with this macro that extracts one sheet and puts it into a e-mail when given a file name in one cell and the sheet name in one cell. But he was unable to make it extract more than one sheet.

Macro:

Sub Macro3()

Dim exportname As String 'declare string
Dim sht1 As String 'declare string
'Dim sht2 As String 'declare string
Dim Mychk

exportname = Range("B3")
sht1 = Range("B4")
'sht2 = Range("B5")

Sheets(sht1).Copy
ActiveWorkbook.SaveAs Filename:=exportname 'this is the first emailed sheet

'check to see if there is a second sheet to be emailed, then copy over to new file
'If (Mychk = IsEmpty(sht2)) Then
'End
'Else
' Windows("Book1.xls").Activate
' Sheets(sht2).Select
' Sheets(sht2).Copy Before:=Workbooks(exportname).Sheets(1)
'End If


'Email as an attachment
Application.Dialogs(xlDialogSendMail).Show


End Sub

B Schwarz