Copy sheet without code?

[What-A-Tool]

Hello -
I have a macro that copies a sheet out of "ThisWorkbook", pastes it into
a new workbook, and then e-mails the new workbook to a recipient list. This
works fine.
My problem is, the "Sheet" object contains code - OnSelectionChange,
AfterDoubleClick, AfterRightClick Events. When the sheet is copied, this
code is also copied into the new book, so when the recipient opens and
selects a cell, the SelectionChanged event fires, causing an error. Thought
I could just throw in an error handler and avoid this, but it throws a
"Compile Error" and opens the debugger every time, which is confusing the
hell out of the recipients.
Is there a way to copy the sheet without the code, or programatically
erase the code?

Thanks for any help -
Sean

 

[Ron de Bruin]

Hi Sean

My SendMail add in have this option
http://www.rondebruin.nl/mail/add-in.htm

Or look here
http://www.cpearson.com/excel/vbe.htm

Another option is to copy all cells to a new worksheet and send that sheet

 

[What-A-Tool]

Hi Sean

My SendMail add in have this option
http://www.rondebruin.nl/mail/add-in.htm

Or look here
http://www.cpearson.com/excel/vbe.htm

Thanks -
haven't had a chance to dig too deep, but the code samples look like
exactly what I need

 

[What-A-Tool]

Hi Sean

My SendMail add in have this option
http://www.rondebruin.nl/mail/add-in.htm

Or look here
http://www.cpearson.com/excel/vbe.htm

Another option is to copy all cells to a new worksheet and send that sheet

Decided to give some of the code from the CPearson site you suggested a try.
The following, slightly modified code, ran without error, but failed to
delete any code.
I am trying to delete code from a Sheet module (in another book) - at first
I thought maybe I couldn't deleete from a sheet, so I imported another code
module and tried - didn't delete anything from any modules.

ThisWorkbook is running the code to delete code from another workbook - is
that possible?

Public Sub DeleteAllCodeInModule(ByVal OtherBookName As String, _
ByVal Sheet2DelCodeFrom As Integer)
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim HowManyLines As Long
Dim OtherBook As Workbook

Set OtherBook = Workbooks(OtherBookName)

Set VBCodeMod =
OtherBook.VBProject.VBComponents(Sheet2DelCodeFrom).CodeModule
With VBCodeMod
StartLine = 1
HowManyLines = .CountOfLines
.DeleteLines StartLine, HowManyLines
End With

End Sub

Public Sub DeleteAllVBA(ByVal OtherBookName As String)

Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents
Dim OtherBook As Workbook

Set OtherBook = Workbooks(OtherBookName)
Set VBComps = OtherBook.VBProject.VBComponents

For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp

End Sub

 

[What-A-Tool]

Decided to give some of the code from the CPearson site you suggested a

try.
The following, slightly modified code, ran without error, but failed to
delete any code.
I am trying to delete code from a Sheet module (in another book) - at
first I thought maybe I couldn't deleete from a sheet, so I imported
another code module and tried - didn't delete anything from any modules.

ThisWorkbook is running the code to delete code from another workbook - is
that possible?

The code does work on ThisWorbook - was able to delete code from a book
running the code.
Is it possible to delete code from another book?

 

[Ron de Bruin]

The following, slightly modified code, ran without error, but failed to
No code ?

 

[What-A-Tool]

No code ?

No - it deleted nothing from the other workbook.
I changed the code back to the original references to "ThisWorkBook" and was
able to delete code from "ThisWorkBook" - but when I changed it to reference
another book, it ran cleanly, but deleted nothing.

 

[Ron de Bruin]

Hi What-A-Tool

This is working OK for the activeworkbook
No reference needed in the example

Public Sub DeleteAllVBA()
Dim VBComp As Object
Dim VBComps As Object
Set VBComps = ActiveWorkbook.VBProject.VBComponents
For Each VBComp In VBComps
Select Case VBComp.Type
Case 1, 3, _
2
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp
End Sub

 

[What-A-Tool]

Hi What-A-Tool

This is working OK for the activeworkbook
No reference needed in the example

Public Sub DeleteAllVBA()
Dim VBComp As Object
Dim VBComps As Object
Set VBComps = ActiveWorkbook.VBProject.VBComponents
For Each VBComp In VBComps
Select Case VBComp.Type
Case 1, 3, _
2
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp
End Sub

Thanks Ron - I'll give it a go. Didn't think to try the ActiveBook thing -
sometimes I have a way of seeing the difficult way of doing things before
the obvious.

Thanks again for the help - Sean