Dropping a sheet macro when saving to another file

[hsummer]

when I copy a sheet to another workbook (via VBA, or course) how can i drop any VBA code imbedded in the sheet?

Specifically, I don't want a user to encounter the macro message when opening the resulting new file.

Thanks,
Harold

 

[acron]

The following example should work for a sheet named Sheet1

Dim VBComp As Object
Set VBComp = Application.VBE.ActiveVBProject.VBComponents("Sheet1&quot
With VBComp.CodeModule
.DeleteLines 1, .countoflines
End With

A.C.

 

[VulcanJedi]

I haven't done this specifically, but I believe it to work. You can COPY the specific sheet to a new workbook. I have done all the code regarding the COPY sheet / save workbook but not togther (although I have thought about it too )
Anyways when you 'copy sheet' it doesn't copy any embedded event codes and a new workbook obviously wouldn't have any modules either.