Save Workbook or worksheet

[Hugh]

Hi there,

How to save a workbook or worksheet without saving the
code? I want to save a single worksheet on a workbook
with columns of data and a chart. However, a form will
pop up if saved file is open since there are code in
workbook. How do I save worksheet (or workbook)without
saving the code? Thanks in advance.

Hugh

 

[Myrna Larson]

For a worksheet: Edit menu/Move or Copy Sheet, Copy it to another workbook and
save that.

For a workbook with many sheets, all to be saved, you would have to go to the
VB Editor and remove all standard modules, forms, and classes, manually remove
all code from ThisWorkbook and Sheet modules.

 

[Hugh]

Hi Myrna,

Thanks for your reply. Let's say there is only one sheet
and I want to save. I use VBA to programatically save the
worksheet. The question is then how to programatically
remove the code and save. Thanks again.

Hugh

 

[Tom Ogilvy]

Previously posted by Jim Rech:

http://groups.google.com/groups?threadm=e3uxzQP0BHA.2228@tkmsftngp07


''Needs a reference to the VB Extensibility library set
'Removes from active workbook all:
''Regular modules
''Class modules
''Userforms
''Code in sheet and workbook modules
''Non built-in references
''Excel 4 macro sheets
''Dialog sheets
Sub RemoveAllCode()
Dim VBComp As Object, AllComp As Object, ThisProj As Object
Dim ThisRef As Reference, WS As Worksheet, DLG As DialogSheet
Set ThisProj = ActiveWorkbook.VBProject
Set AllComp = ThisProj.VBComponents
For Each VBComp In AllComp
With VBComp
Select Case .Type
Case vbext_ct_StdModule, vbext_ct_ClassModule, _
vbext_ct_MSForm
AllComp.Remove VBComp
Case vbext_ct_Document
.CodeModule.DeleteLines 1, .CodeModule.CountOfLines
End Select
End With
Next
For Each ThisRef In ThisProj.References
If Not ThisRef.BuiltIn Then ThisProj.References.Remove ThisRef
Next
Application.DisplayAlerts = False
For Each WS In Excel4MacroSheets
WS.Delete
Next
For Each DLG In DialogSheets
DLG.Delete
Next
End Sub