Hello,

I need to develop a macro that automatically and silently saves a copy of an Excel Document to a location while stripping the VBA code that copies it. All of this needs to be automated with no user interaction.

This is what I have so far.

ThisWorkbook


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnTime dTime, "MyMacro", , False
End Sub
 
Private Sub Workbook_Open()
    dTime = Now + TimeValue("00:00:05")
    Application.OnTime dTime, "MyMacro"
End Sub
Module 1

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnTime dTime, "MyMacro", , False
End Sub
 
Private Sub Workbook_Open()
    dTime = Now + TimeValue("00:00:05")
    Application.OnTime dTime, "MyMacro"
End Sub
     
    ActiveWorkbook.SaveCopyAs Filename:="C:\Temp\Test" & _
         Replace(ActiveWorkbook.Name, ".xlsm", _
         " " & Format(Now, "yyyy-mm-dd-hh-mm") & ".xls")
     
End Sub
This code works great for my needs except the copy opens and attempts to run the macro on the "Loop schedule" and displays a debug error message. I need the copy stripped of the macro so the sheet wont display the error code.

Any help is greatly appreciated.

Thanks

-Matt