Copy sheet without code?
 

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

Copy sheet without code?
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"What-A-Tool" wrote in message ...
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

Copy sheet without code?
 

"Ron de Bruin" wrote in message
...
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

Copy sheet without code?
 

"Ron de Bruin" wrote in message
...
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

Copy sheet without code?
 

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?

Copy sheet without code?
 

The following, slightly modified code, ran without error, but failed to
delete any code.

No code ?



--
Regards Ron de Bruin
http://www.rondebruin.nl


"What-A-Tool" wrote in message ...
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?

Copy sheet without code?
 

"Ron de Bruin" wrote in message
...
The following, slightly modified code, ran without error, but failed to
delete any code.

No code ?



--
Regards Ron de Bruin
http://www.rondebruin.nl


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.

Copy sheet without code?
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"What-A-Tool" wrote in message ...

"Ron de Bruin" wrote in message
...
The following, slightly modified code, ran without error, but failed to
delete any code.

No code ?



--
Regards Ron de Bruin
http://www.rondebruin.nl


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.

Copy sheet without code?
 

"Ron de Bruin" wrote in message
...
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