Copying sheets without code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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.
 
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 :
 
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.
 
Back
Top