Programically remove code from a worksheet

[Stephen sjw_ost]

I have a workbook that I need to copy a particular sheet out to save in a
separate workbook. This part is done and works.
The worksheet has an Activate piece that shows a user form. When I copy the
worksheet into its own workbook, programically, the code comes with it. When
the sheet is activated in the new workbook the code erros out because the
user form is not copied over with it, the userform is not needed in the new
workbook. How can I programically removed the Worksheet_Activate code when
the worksheet is programically copied out?

Thanks for any help

 

[FSt1]

hi
see this site
http://cpearson.com/excel/vbe.aspx
make note of the warning.

Regards
FSt1

 

[Jim Cone]

You may be happier copying and pasting the data vs. trying to delete the code.
I avoid writing code that alters code if at all possible.
If you must/want/have to do that then Chip Pearson's website has instructions...
http://www.cpearson.com/excel/vbe.aspx

Alternatively, something like the following can be used...
'--
Sub PutErThere()
Dim rng As Range
'Find the bottom right cell
Set rng = ThisWorkbook.Worksheets _
("Sludge").Cells.SpecialCells(xlCellTypeLastCell)
'Establish the range to copy
Set rng = ThisWorkbook.Worksheets _
("Sludge").Range("A1", rng)
'Copy and paste
rng.Copy _
Destination:=Workbooks("NewBook").Worksheets(1).Range("A1")
'Clean up
Application.CutCopyMode = False
Set rng = Nothing
End Sub
--
Jim Cone
Portland, Oregon USA




"Stephen sjw_ost"
<[email protected]>
wrote in message
I have a workbook that I need to copy a particular sheet out to save in a
separate workbook. This part is done and works.
The worksheet has an Activate piece that shows a user form. When I copy the
worksheet into its own workbook, programically, the code comes with it. When
the sheet is activated in the new workbook the code erros out because the
user form is not copied over with it, the userform is not needed in the new
workbook. How can I programically removed the Worksheet_Activate code when
the worksheet is programically copied out?

Thanks for any help

 

[paulgu]

Stephen,
Instead of programmatically removing the Worksheet_Activate code, why not put a test in that code for something that is present in the orginal workbook but not in the new workbook - another worksheet perhaps? If the test fails, don't try to open the form and you don't get an error.
HTH
Paul G