deactivate macro after "save as"

[ReneY]

Hi everyone

I'm writing a code for a program that compiles information from different excel files into another excel file and renames it; that works fine, the problem I have is that when you open this new file that was created with the macro, all the code is saved in it too (as it just "renames and saves" itself).

The code that I use for renaming the file is:

" ActiveWorkbook.SaveAs Filename:= _"Compilation.xls" "

How can I do to have a "clean" file without the code, or at least not running the "Workbook_Open" sub? (Because it hides all the sheets and pops out the user form)

I'm sure creating a subroutine will be the solution, I just have no idea how to command it to "stop" or "deactivate" the macro from keep running.

Cheers

ReneY

[MaximS]

if you have just a few sheets you can select and past the data into another (clean) workbook and then save it as.

[lucas]

I run this from an addin. You should be able to incorporate it into your code....It's from one of Jakes KB entries but the kb is not available right now.

[VBA]Sub DeleteAllCode()
'Trust Access To Visual Basics Project must be enabled.
'From Excel: Tools | Macro | Security | Trusted Sources
Dim x As Integer
Dim Proceed As VbMsgBoxResult
Dim Prompt As String
Dim Title As String
Prompt = "Are you certain that you want to delete all the VBA Code from " & _
ActiveWorkbook.Name & "?"
Title = "Verify Procedure"

Proceed = MsgBox(Prompt, vbYesNo + vbQuestion, Title)
If Proceed = vbNo Then
MsgBox "Procedure Canceled", vbInformation, "Procedure Aborted"
Exit Sub
End If

On Error Resume Next
With ActiveWorkbook.VBProject
For x = .VBComponents.Count To 1 Step -1
.VBComponents.Remove .VBComponents(x)
Next x
For x = .VBComponents.Count To 1 Step -1
.VBComponents(x).CodeModule.DeleteLines _
1, .VBComponents(x).CodeModule.CountOfLines
Next x
End With
On Error GoTo 0

End Sub
[/VBA]