Private Sub Workbook.Open()
' you only need to change the first one
ActiveWorkbook.Worksheets("Daily Email").copy
' now the activesheet/activeworkbook is the copy
ActiveSheet.usedrange.formula = Activesheet.usedrange.value
Activeworkbook.SaveAs "C:\Archive\" & format(date-1,"yyyymmdd") & ".xls"
ActiveWorkbook.SendMail "
ActiveWorkbook.Close SaveChanges:=False
End Sub



"S Davis" wrote in message
oups.com...
On Feb 13, 11:41 am, "Tom Ogilvy" wrote:
create a new sheet lacking formulas but containing the values and
formatting

Sub MakeRecord()
activesheet.copy
activesheet.usedrange.formula = activesheet.usedrange.value
Activeworkbook.SaveAs "C:\Archive\" & format(date-1,"yyyymmdd") & ".xls"
ActiveWorkbook.Close SaveChanges:=False
End Sub

Thanks Tom. I'm using this. I will do a search after this post, but
for completeness sake it would be great if I could get a response on
how to replace all "activesheet" with a worksheet name. Worksheet is
titled "Daily Email".