Macro - SaveCopyAs and Remove VBA Code

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

Don't know where the Loop Schedule macro is, but consider saving it as an xlsx format file? That will remove any macro in the workbook.

I did try just saving as a .xlsx but everytime I do that I get a "File is corrupt" error when opening the copy.

I noticed that I entered my code wrong. The "This Workbook" code is correct but the Module 1 code should be as below.

Public dTime As Date
Sub MyMacro()
    dTime = Now + TimeValue("00:01:00")
    Application.OnTime dTime, "MyMacro"
     
    ActiveWorkbook.SaveAs Filename:="C:\Temp\ITEST" & Format(Now, "-yyyy-mmdd-hhmm") & ".xlsm", FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
     
     
End Sub

Try saving it using

FileFilter:=xlOpenXMLWorkbook

This should save it into a valid xlsx excel file.

Note: and change the save name to

& "*.xlsx"