Copy to new spreadsheet
 

Is there a way to make an excel sheet covered in formulas
copy itself and create a new spreadsheet with values
instead of formulas to be emailed as just one sheet of
information?

I don't know where to start, I am quite new to vba so be
gentle!

Lynda

Linda

this copies the activesheet to a new workbook, kills the formulas and asks
for an email address to mail to and then mails it. It can of course be
refined, but maybe it's a start for you

Sub MoveActiveSheetKillFormulasAndEmail()
Dim wb As Workbook
Dim wks As Worksheet, rng As Range
Dim sEmail As String
ActiveSheet.Copy
Set wb = ActiveWorkbook
Set wks = ActiveSheet
Set rng = wks.Cells.SpecialCells(xlCellTypeFormulas)
rng.Copy
rng.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
sEmail = InputBox("Enter the email address", "Email Address")
wb.SendMail Recipients:=sEmail, Subject:="Here's the worksheet"
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS

"Lynda" wrote in message
...
Is there a way to make an excel sheet covered in formulas
copy itself and create a new spreadsheet with values
instead of formulas to be emailed as just one sheet of
information?

I don't know where to start, I am quite new to vba so be
gentle!

Lynda