Need to extract sheets out of workbook into new file



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.



-----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.


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.


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")

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
' Windows("Book1.xls").Activate
' Sheets(sht2).Select
' Sheets(sht2).Copy Before:=Workbooks(exportname).Sheets(1)
'End If

'Email as an attachment

End Sub

B Schwarz

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question