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" <(E-Mail Removed)> wrote in message news:733EF829-B497-4018-91D4-(E-Mail Removed)...
> 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.