Hi Per,

thanks for your assistance... problem is that i get the following error (and
I do not want to change my security level from medium):
Run-time error '1004'
Programmatic access to Visual Basic project is not trusted

Thanks

Les
"Per Jessen" wrote:

Thanks Ron,

It should have been:

With ActiveWorkbook.VBProject...

---
Per

"Ron de Bruin" skrev i meddelelsen
...
Look out

This will remove the code from the original workbook

You use ThisWorkbook in the code

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Per Jessen" wrote in message
...
Assuming the sheet don't have any code which you want to keep, try this:

Sub ExtractHL()
'
' ExtractHL Macro
' Extract and send the HL file to customers
Sheets("daily hectolitres").Copy

' Delete event code in copied sheet
Dim strName As String
strName = ActiveSheet.CodeName
With ThisWorkbook.VBProject.VBComponents(strName).CodeM odule
.DeleteLines 1, .CountOfLines
End With

' Open email and attach file to email
ActiveWorkbook.SaveAs Filename:="daily hectolitre.xls"
ActiveWorkbook.ChangeFileAccess xlReadOnly
Application.Dialogs(xlDialogSendMail).Show "ABI- Daily Hectolitres",
"ABI daily hectolitres"
ActiveWorkbook.Close False
Kill "daily hectolitre.xls"
Sheets("daily procedure").Select
Range("C3").Select

End Sub

Regards,
Per

"LesG" skrev i meddelelsen
...
I have code in a workbook that copies a worksheet to send by email... The
code works perfectly, but the sheet I am copiyng has code that I want to
remove before emailing...

The code in the workbook module is as follows:
Sub ExtractHL()
'
' ExtractHL Macro
' Extract and send the HL file to customers
Sheets("daily hectolitres").Copy

' Open email and attach file to email
ActiveWorkbook.SaveAs Filename:="daily hectolitre.xls"
ActiveWorkbook.ChangeFileAccess xlReadOnly
Application.Dialogs(xlDialogSendMail).Show "ABI- Daily Hectolitres",
"ABI daily hectolitres"
ActiveWorkbook.Close False
Kill "daily hectolitre.xls"
Sheets("daily procedure").Select
Range("C3").Select

End Sub

The code that I want to remove from the copied workbook is:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("I4:I34")) Is Nothing Then
ActiveCell.Offset(-1, 10).Range("A1").Select
End If
If Not Intersect(Target, Me.Range("S4:S34")) Is Nothing Then
ActiveCell.Offset(0, -10).Range("A1").Select
Sheet15.Activate
End If
End Sub

Any suggestions please


__________ Information from ESET Smart Security, version of virus
signature database 3953 (20090321) __________

The message was checked by ESET Smart Security.

http://www.eset.com



__________ Information from ESET Smart Security, version of virus
signature database 3953 (20090321) __________

The message was checked by ESET Smart Security.

http://www.eset.com