If the line is as you show it, then remove the space from between This and
Workbook... the correct reference is ThisWorkbook

--
Rick (MVP - Excel)


"CB" wrote in message
...
Hi Joel,

Thanks for responding. I thought I would try this. First, the debugger
didn't like the fact that you had "next sht" twice. Once I deleted the
last
instance, compiled, then ran the code, I received the following error...

Run-time Error "438"
Object doesn't support this property or method.

The debugger then stops on the line:

For each sht in This Workbook.

Thanks again.

Chris

"Joel" wrote:

Instead of creating a macro to delete the macros from the workbook.
Instead
create a new workbook and copy the sheet to new book.

Sub CreateCopy

First = true
for each sht in thisworkbook
If First = True then
sht.copy 'create new workbook
set NewBk = Activeworkbook
First = False
else
with NewBk
sht.copy after:=.sheets(.sheets.count)
end with
end if
next sht
NewBk.SAveas filename:="book2.xls"
next sht
end sub


"CB" wrote:

Hi everyone,

Programming in Excel is rather new to me. Ive been able to get by thus
far
by recording macros and looking at the code. I have been able to
manipulate
the code by referring to the VBE help and these news groups.

Im looking for assistance in modifying my code such that when the file
is
saved (SAVE AS actually), all code is removed from the new file but not
the
current file. Ive searched these discussions groups and found a
reference to
http://www.cpearson.com/excel/VBE.aspx where the following code will
delete
all VBA in a project.

Sub DeleteAllVBACode()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule

Set VBProj = ActiveWorkbook.VBProject

For Each VBComp In VBProj.VBComponents
If VBComp.Type = vbext_ct_Document Then
Set CodeMod = VBComp.CodeModule
With CodeMod
.DeleteLines 1, .CountOfLines
End With
Else
VBProj.VBComponents.Remove VBComp
End If
Next VBComp
End Sub

What I am not clear on is whether or not this code will delete all code
in
the workbook that is currently open or whether it will delete the code
in the
new workbook that is created when my code does a €œsave as€�. The first
line
under the declarations makes me think perhaps it deletes the code in
the
workbook currently opened.

Am I correct?

FWIW, Im including the code that executes when my user clicks the
command
button to save the file. Sorry in advance if the lines don't wrap
properly.

Private Sub SaveData_Click()

If Trim(Worksheets("Post-Service").Range("D3").Value = "") Then
MsgBox ("You must enter a serial number.")
Exit Sub
Else
Worksheets("Post-Service").Range("D3") =
UCase(Worksheets("Post-Service").Range("D3"))
If Left(Worksheets("Post-Service").Range("D3"), 1) = "C" Then
ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" &
Range("d3")
& "_" & Format(Now, "yyyymmmdd") & ".xls"
Else
If MsgBox("Are you sure the serial number doesn't begin
with
C?", vbYesNo) = vbYes Then
ActiveWorkbook.SaveCopyAs "\\MyPath\" & "SR50_SN_" &
Range("d3")
& "_" & Format(Now, "yyyymmmdd") & ".xls"
Else
MsgBox ("Please fix the serial number.")
End If
End If
End If
End Sub

Thanks for any and all assistance.

Chris