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

Hi

Just before using SaveAs, copy the required sheets to another window like;
for copying sheet1 and sheet2.

Sheets(Array("Sheet1", "Sheet2")).Select
Sheets(Array("Sheet1", "Sheet2")).Copy

and then use SaveAs to save it into a temporary location. Attach that to the
mail and then delete the file from temporary location. Please try and let me
know for further help.

If this post helps click Yes
--------------
Jacob Skaria


"LesG" wrote:

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

Have you checked to see if the macro is actually in the workbook that gets
mails. I think not!

The following line creates a newworkbook with only one worksheet and no macro.

Sheets("daily hectolitres").Copy

When you perform a COPY of a worksheet and you don't specify either AFTER or
BEFORE a new workbook gets created. This new workbook is what gets e-mailed.

This new workbook is what gets placed in the E-Mail



"LesG" wrote:

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

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

another way:

Sheets("daily hectolitres").Move

will move that sheet directly to a newly created workbook




U¿ytkownik "Joel" napisa³ w wiadomo¶ci
...
Have you checked to see if the macro is actually in the workbook that gets
mails. I think not!

The following line creates a newworkbook with only one worksheet and no
macro.

Sheets("daily hectolitres").Copy

When you perform a COPY of a worksheet and you don't specify either AFTER
or
BEFORE a new workbook gets created. This new workbook is what gets
e-mailed.

This new workbook is what gets placed in the E-Mail



"LesG" wrote:

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

Untested but try this if you have code in the sheet module

Sub test()
Dim sh As Worksheet
Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String

FileExtStr = ".xls": FileFormatNum = -4143
TempFilePath = Environ$("temp") & "\"
TempFileName = "daily hectolitre"

Set sh = ThisWorkbook.Sheets("daily hectolitres")
Set wb = Workbooks.Add(1)
wb.Sheets(1).Name = "daily hectolitres"
sh.Cells.Copy wb.Sheets(1).Cells(1)

With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, _
FileFormat:=FileFormatNum
On Error Resume Next
.SendMail "", _
"This is the Subject line"
On Error GoTo 0
.Close SaveChanges:=False
End With

'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr

End Sub


--

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




"LesG" wrote in message ...
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

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

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

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

See my reply

--

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




"LesG" wrote in message ...
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






__________ 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

Hi Ron,

Hi from a warm South African evening... as ever your solution was spot on...
I replaced the code within my "Sub ExtractHL()" with your code and modified
the email code (I personally prefer
"Application.Dialogs(xlDialogSendMail).Show "ABI- Daily Hectolitres", "ABI
daily hectolitres") and it worked a dream...

You are a star

Thanks

Les



"Ron de Bruin" wrote:

Untested but try this if you have code in the sheet module

Sub test()
Dim sh As Worksheet
Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String

FileExtStr = ".xls": FileFormatNum = -4143
TempFilePath = Environ$("temp") & "\"
TempFileName = "daily hectolitre"

Set sh = ThisWorkbook.Sheets("daily hectolitres")
Set wb = Workbooks.Add(1)
wb.Sheets(1).Name = "daily hectolitres"
sh.Cells.Copy wb.Sheets(1).Cells(1)

With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, _
FileFormat:=FileFormatNum
On Error Resume Next
.SendMail "", _
"This is the Subject line"
On Error GoTo 0
.Close SaveChanges:=False
End With

'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr

End Sub


--

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




"LesG" wrote in message ...
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

I understtod the code you wrote to remove the macro from the new sheet but
when the original sheet macro tries to execute this code, I get the following
error message:
"Programatic access to Visual Basic Project is not trusted"

Do anybody knows how can I eliminate this?

"Ron de Bruin" wrote:

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