I have Sheet1 in Workbook1. Workbook1 also has Module1 which contains
Procedure1. Sheet1 has an on change event that calls Procedure1. I want to
publish Sheet1 to a new file (Workbook2) so I use the Sheets("Sheet1").Copy
command to do this. The problem is, I also want to delete some columns and
rows from Sheet1 in Workbook2 and, when I do this, it triggers the on change
event (because this code was also copied with Sheet1 when I copied it into
Workbook2) which tries to call Procedure1 which doesn't exist in Workbook2 so
I crash.

Question - how can I copy a sheet to a new workbook but not copy the code
for that sheet that existed in the orginal workbook? In other words, I do
not want Sheet1 in Workbook2 to have an on change event. I know I have run
into this before and it seems like there was a simple solution but it escapes
me now. Thanks in advance to anyone that can offer a solution.

why not just right click the tab, select View Code and delete the code?

"Todd" wrote:

I have Sheet1 in Workbook1. Workbook1 also has Module1 which contains
Procedure1. Sheet1 has an on change event that calls Procedure1. I want to
publish Sheet1 to a new file (Workbook2) so I use the Sheets("Sheet1").Copy
command to do this. The problem is, I also want to delete some columns and
rows from Sheet1 in Workbook2 and, when I do this, it triggers the on change
event (because this code was also copied with Sheet1 when I copied it into
Workbook2) which tries to call Procedure1 which doesn't exist in Workbook2 so
I crash.

Question - how can I copy a sheet to a new workbook but not copy the code
for that sheet that existed in the orginal workbook? In other words, I do
not want Sheet1 in Workbook2 to have an on change event. I know I have run
into this before and it seems like there was a simple solution but it escapes
me now. Thanks in advance to anyone that can offer a solution.

Rather then delete the code decide if you want it to run or not.

Private Sub Worksheet_Change(ByVal Target As Range)
If Worksheets(1).Parent.Name = "Book1" Then
Exit Sub
Else
Call Procedure1
End If
End Sub


"Todd" wrote:

I have Sheet1 in Workbook1. Workbook1 also has Module1 which contains
Procedure1. Sheet1 has an on change event that calls Procedure1. I want to
publish Sheet1 to a new file (Workbook2) so I use the Sheets("Sheet1").Copy
command to do this. The problem is, I also want to delete some columns and
rows from Sheet1 in Workbook2 and, when I do this, it triggers the on change
event (because this code was also copied with Sheet1 when I copied it into
Workbook2) which tries to call Procedure1 which doesn't exist in Workbook2 so
I crash.

Question - how can I copy a sheet to a new workbook but not copy the code
for that sheet that existed in the orginal workbook? In other words, I do
not want Sheet1 in Workbook2 to have an on change event. I know I have run
into this before and it seems like there was a simple solution but it escapes
me now. Thanks in advance to anyone that can offer a solution.

Here is a little more information. The publishing will be part of a loop.
Each employee is going to need to publish between 20 and 50 statements for
their area of responsibility. Since the macro is being designed to be part
of a loop I want the code to delete the on change code each time a statement
is produced. Basically I want the employee to be able to hit publish and
have all statements automatically produced without manual intervention (like
right clicking on the tab). Is there a way to copy a sheet but not any code
on that sheet? That will allow me to do what I need to do. Thanks.

"Patrick Molloy" wrote:

why not just right click the tab, select View Code and delete the code?

"Todd" wrote:

I have Sheet1 in Workbook1. Workbook1 also has Module1 which contains
Procedure1. Sheet1 has an on change event that calls Procedure1. I want to
publish Sheet1 to a new file (Workbook2) so I use the Sheets("Sheet1").Copy
command to do this. The problem is, I also want to delete some columns and
rows from Sheet1 in Workbook2 and, when I do this, it triggers the on change
event (because this code was also copied with Sheet1 when I copied it into
Workbook2) which tries to call Procedure1 which doesn't exist in Workbook2 so
I crash.

Question - how can I copy a sheet to a new workbook but not copy the code
for that sheet that existed in the orginal workbook? In other words, I do
not want Sheet1 in Workbook2 to have an on change event. I know I have run
into this before and it seems like there was a simple solution but it escapes
me now. Thanks in advance to anyone that can offer a solution.

The best way is to use this in the loop to create a new workbook with one sheet

Workbooks.Add(1)

Then let the code copy the data in this empty sheet


--

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




"Todd" wrote in message ...
Here is a little more information. The publishing will be part of a loop.
Each employee is going to need to publish between 20 and 50 statements for
their area of responsibility. Since the macro is being designed to be part
of a loop I want the code to delete the on change code each time a statement
is produced. Basically I want the employee to be able to hit publish and
have all statements automatically produced without manual intervention (like
right clicking on the tab). Is there a way to copy a sheet but not any code
on that sheet? That will allow me to do what I need to do. Thanks.

"Patrick Molloy" wrote:

why not just right click the tab, select View Code and delete the code?

"Todd" wrote:

I have Sheet1 in Workbook1. Workbook1 also has Module1 which contains
Procedure1. Sheet1 has an on change event that calls Procedure1. I want to
publish Sheet1 to a new file (Workbook2) so I use the Sheets("Sheet1").Copy
command to do this. The problem is, I also want to delete some columns and
rows from Sheet1 in Workbook2 and, when I do this, it triggers the on change
event (because this code was also copied with Sheet1 when I copied it into
Workbook2) which tries to call Procedure1 which doesn't exist in Workbook2 so
I crash.

Question - how can I copy a sheet to a new workbook but not copy the code
for that sheet that existed in the orginal workbook? In other words, I do
not want Sheet1 in Workbook2 to have an on change event. I know I have run
into this before and it seems like there was a simple solution but it escapes
me now. Thanks in advance to anyone that can offer a solution.

Its Possible sure... Have a look here

http://www.cpearson.com/Excel/vbe.aspx

"Todd" wrote:

Here is a little more information. The publishing will be part of a loop.
Each employee is going to need to publish between 20 and 50 statements for
their area of responsibility. Since the macro is being designed to be part
of a loop I want the code to delete the on change code each time a statement
is produced. Basically I want the employee to be able to hit publish and
have all statements automatically produced without manual intervention (like
right clicking on the tab). Is there a way to copy a sheet but not any code
on that sheet? That will allow me to do what I need to do. Thanks.

"Patrick Molloy" wrote:

why not just right click the tab, select View Code and delete the code?

"Todd" wrote:

I have Sheet1 in Workbook1. Workbook1 also has Module1 which contains
Procedure1. Sheet1 has an on change event that calls Procedure1. I want to
publish Sheet1 to a new file (Workbook2) so I use the Sheets("Sheet1").Copy
command to do this. The problem is, I also want to delete some columns and
rows from Sheet1 in Workbook2 and, when I do this, it triggers the on change
event (because this code was also copied with Sheet1 when I copied it into
Workbook2) which tries to call Procedure1 which doesn't exist in Workbook2 so
I crash.

Question - how can I copy a sheet to a new workbook but not copy the code
for that sheet that existed in the orginal workbook? In other words, I do
not want Sheet1 in Workbook2 to have an on change event. I know I have run
into this before and it seems like there was a simple solution but it escapes
me now. Thanks in advance to anyone that can offer a solution.