Copying sheets without code
 

How can I move/copy a sheet into another workbook without taking the code
attached to the sheet with it?

In particular I have a menu that should only appear when a particular
worksheet is active. This is achieved through the activate and de-activate
events. When I copy the sheet to another workbook (through code) the event
handlers go with it (and crash since they can't find the function).

Do I have to go into the module behind the copied worksheet and delete the
code?

Regards

Les
--
Les Gordon

Copying sheets without code
 

try..

Sub CopySansCode()
Dim wsSrc As Worksheet
Dim wsDst As Worksheet
Set wsSrc = ActiveSheet
Set wsDst = Worksheets.Add
wsSrc.Cells.Copy wsDst.Cells(1)

End Sub

It does have some drawbacks..as not everything is transferred

comments/formats/shapes/objects etc ARE transferred
pagesetup is (obviously) NOT transferred

AND you may lose "globally defined" rangenames.
(which when you'd use copy on the sheet
would be translated to "local defined names"

but then again it may work well in your situation.



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

?B?TGVzIEdvcmRvbg==?= wrote in message
:

How can I move/copy a sheet into another workbook without taking the
code attached to the sheet with it?

In particular I have a menu that should only appear when a particular
worksheet is active. This is achieved through the activate and
de-activate events. When I copy the sheet to another workbook (through
code) the event handlers go with it (and crash since they can't find
the function).

Do I have to go into the module behind the copied worksheet and delete
the code?

Regards

Les

Copying sheets without code
 

a vague suggestion

why not save the file and in the saved file remove module .
for removing module my notes from this newsgroup shows.
==================
With thisworkbook.VBProject
.VBComponents.Remove .VBComponents("Module1")
End With
============================
I am sorry I forgot to notedown the name of the MVP who gave this code.

keepITcool wrote in message
...
try..

Sub CopySansCode()
Dim wsSrc As Worksheet
Dim wsDst As Worksheet
Set wsSrc = ActiveSheet
Set wsDst = Worksheets.Add
wsSrc.Cells.Copy wsDst.Cells(1)

End Sub

It does have some drawbacks..as not everything is transferred

comments/formats/shapes/objects etc ARE transferred
pagesetup is (obviously) NOT transferred

AND you may lose "globally defined" rangenames.
(which when you'd use copy on the sheet
would be translated to "local defined names"

but then again it may work well in your situation.



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

?B?TGVzIEdvcmRvbg==?= wrote in message
:

How can I move/copy a sheet into another workbook without taking the
code attached to the sheet with it?

In particular I have a menu that should only appear when a particular
worksheet is active. This is achieved through the activate and
de-activate events. When I copy the sheet to another workbook (through
code) the event handlers go with it (and crash since they can't find
the function).

Do I have to go into the module behind the copied worksheet and delete
the code?

Regards

Les

Copying sheets without code
 

Thanks for calling my suggestion "VAGUE",
but PLEASE explain what's VAGUE about it?

At least I mention it's drawbacks, which YOU dont for your "normal"
approach to ripping code from a module.

Namely that It wont run on a standard excel installation...
Unless Access to Visual Basic Project is enabled, and PLEASE note
that in Excel2003 a user cannot change this option unless
he has permission on the HKLM key in the registry.



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


R.VENKATARAMAN wrote :

a vague suggestion

why not save the file and in the saved file remove module .
for removing module my notes from this newsgroup shows.
==================
With thisworkbook.VBProject
.VBComponents.Remove .VBComponents("Module1")
End With
============================
I am sorry I forgot to notedown the name of the MVP who gave this
code.

keepITcool wrote in message
...
try..

Sub CopySansCode()
Dim wsSrc As Worksheet
Dim wsDst As Worksheet
Set wsSrc = ActiveSheet
Set wsDst = Worksheets.Add
wsSrc.Cells.Copy wsDst.Cells(1)

End Sub

It does have some drawbacks..as not everything is transferred

comments/formats/shapes/objects etc ARE transferred
pagesetup is (obviously) NOT transferred

AND you may lose "globally defined" rangenames.
(which when you'd use copy on the sheet
would be translated to "local defined names"

but then again it may work well in your situation.



--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam

?B?TGVzIEdvcmRvbg==?= wrote in message
:

How can I move/copy a sheet into another workbook without taking
the code attached to the sheet with it?

In particular I have a menu that should only appear when a
particular worksheet is active. This is achieved through the
activate and de-activate events. When I copy the sheet to another
workbook (through code) the event handlers go with it (and crash
since they can't find the function).

Do I have to go into the module behind the copied worksheet and
delete the code?

Regards

Les

Copying sheets without code
 

Thanks to both of you for your suggestions; in this case I need the formats
to be preserved so I'll have a go at "ripping out" the code.

"keepITcool" wrote:



Thanks for calling my suggestion "VAGUE",
but PLEASE explain what's VAGUE about it?

At least I mention it's drawbacks, which YOU dont for your "normal"
approach to ripping code from a module.

Namely that It wont run on a standard excel installation...
Unless Access to Visual Basic Project is enabled, and PLEASE note
that in Excel2003 a user cannot change this option unless
he has permission on the HKLM key in the registry.



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


R.VENKATARAMAN wrote :

a vague suggestion

why not save the file and in the saved file remove module .
for removing module my notes from this newsgroup shows.
==================
With thisworkbook.VBProject
.VBComponents.Remove .VBComponents("Module1")
End With
============================
I am sorry I forgot to notedown the name of the MVP who gave this
code.

keepITcool wrote in message
...
try..

Sub CopySansCode()
Dim wsSrc As Worksheet
Dim wsDst As Worksheet
Set wsSrc = ActiveSheet
Set wsDst = Worksheets.Add
wsSrc.Cells.Copy wsDst.Cells(1)

End Sub

It does have some drawbacks..as not everything is transferred

comments/formats/shapes/objects etc ARE transferred
pagesetup is (obviously) NOT transferred

AND you may lose "globally defined" rangenames.
(which when you'd use copy on the sheet
would be translated to "local defined names"

but then again it may work well in your situation.



--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam

?B?TGVzIEdvcmRvbg==?= wrote in message
:

How can I move/copy a sheet into another workbook without taking
the code attached to the sheet with it?

In particular I have a menu that should only appear when a
particular worksheet is active. This is achieved through the
activate and de-activate events. When I copy the sheet to another
workbook (through code) the event handlers go with it (and crash
since they can't find the function).

Do I have to go into the module behind the copied worksheet and
delete the code?

Regards

Les