If you have Excel 2007, use the worksheet.copy method to create a new file
with the single sheet. Save it as an xlsx (you'll need to disable alerts).
Close the file and reopen it. Now it will have lost all code, repeat the
copy to your desired workbook. Close the temporary file and 'Kill' it.
Record a macro for the syntax

In earlier versions you can copy "Cells" from source to a new sheet in a new
workbook (select the intersect of row/column headers. This will copy all the
shapes without code. In 2007 this is highly resource intensive, in earlier
versions it depends on the amount of contents and usedrange. If large, you
can break down into smaller chunks.

You say you want to leave controls (I assume Forms buttons) on the sheet to
call macros. If these are to call similarly named macros in the new workbook
you'll need to edit links in the copied buttons.

One other way is to remove all code from the copied sheet. Doable but only
if access to VB Project is enabled, which some users may be wary of.

Regards,
Peter T



<Craig Remillard wrote in message
...
I am using the worksheet.copy method to copy a worksheet intact. The
source worksheet has a few event macros that I would like to disable in the
destination worksheet.

I considered just copying the contents of the source to a new worksheet.
However, the source worksheet contains some controls I would like to keep
in the destination sheet. These controls call to module subroutines
outside the worksheet.

I can see two possible solutions, but I do not know how to execute any of
them:
1) Disable all worksheet VBA code for the destination worksheet
2) Copy the contents of the source worksheet, and then copy only the
buttons I want to use.

Can someone point me to the method or function I can use for either
strategy? Thanks, Craig.


Submitted via EggHeadCafe - Software Developer Portal of Choice
.NET Abstract Factory
http://www.eggheadcafe.com/tutorials...t-factory.aspx