Auto save and rename file

[Jmtramos]

Hello everyone,

I have a timesheet that I need to send every month to my company. I also have to rename it with the current date (Year and Month) and add a code has shown below.
Off course, it's not right because it gives me an error when opening saying:

My question is, how can I save to xlsx file format and remove all macros from file.

Sub SaveAsNew()
Dim dt As String, wbName As String

wbName = "_KT_TS_KT055I-Jorge Ramos"
dt = Format(CStr(Now), "YYYYMM")
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs FileName:="C:\TimeSheet\" & dt & wbName & ".xlsx"
End Sub

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
If Success = True Then Call SaveAsNew
End Sub

Many thanks to all!

 

[GWteB]

Hi Jmtramos, welcome to this board.
The code below should do what you're after. Put this code in the workbook of which you want to have two different versions.

This goes in the ThisWorkbook module:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    If Success Then SaveWithoutMacros
End Sub

This goes in a standard module:

Public Sub SaveWithoutMacros()
    Dim dt As String, wbName As String

    wbName = "_KT_TS_KT055I-Jorge Ramos"
    dt = Format(CStr(Now), "YYYYMM")
    Application.DisplayAlerts = False

' end up with original file on desktop
    Dim sOrgWbFullName  As String
    sOrgWbFullName = ThisWorkbook.FullName

' disable the Workbook_AfterSave event macro to avoid endless recursion
    Application.EnableEvents = False

' save copy of workbook without macro's
' this running macro however stays within memory and will proceed (!!)
    ThisWorkbook.SaveAs Filename:="C:\TimeSheet\" & dt & wbName & ".xlsx", _
                        FileFormat:=xlOpenXMLWorkbook, _
                        ConflictResolution:=xlLocalSessionChanges
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    
' return to where we left of ===
    Application.Workbooks.Open Filename:=sOrgWbFullName
               
' === release current .xlsx (!!) workbook and its macros from memory ===
    ThisWorkbook.Close
           
    MsgBox "This message will never be displayed..."
End Sub

 

[Jmtramos]

Hi Jmtramos, welcome to this board.
The code below should do what you're after. Put this code in the workbook of which you want to have two different versions.

This goes in the ThisWorkbook module:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    If Success Then SaveWithoutMacros
End Sub

This goes in a standard module:

Public Sub SaveWithoutMacros()
    Dim dt As String, wbName As String

    wbName = "_KT_TS_KT055I-Jorge Ramos"
    dt = Format(CStr(Now), "YYYYMM")
    Application.DisplayAlerts = False

' end up with original file on desktop
    Dim sOrgWbFullName  As String
    sOrgWbFullName = ThisWorkbook.FullName

' disable the Workbook_AfterSave event macro to avoid endless recursion
    Application.EnableEvents = False

' save copy of workbook without macro's
' this running macro however stays within memory and will proceed (!!)
    ThisWorkbook.SaveAs Filename:="C:\TimeSheet\" & dt & wbName & ".xlsx", _
                        FileFormat:=xlOpenXMLWorkbook, _
                        ConflictResolution:=xlLocalSessionChanges
    Application.EnableEvents = True
    Application.DisplayAlerts = True
   
' return to where we left of ===
    Application.Workbooks.Open Filename:=sOrgWbFullName
              
' === release current .xlsx (!!) workbook and its macros from memory ===
    ThisWorkbook.Close
          
    MsgBox "This message will never be displayed..."
End Sub

Hello GWteb,

Thanks a lot for the help.
I will try to change my code and will tell you if it works.

 

[Jmtramos]

Once again, I want to thank you. It worked on the first try.

 

[GWteB]

You're welcome and thanks for letting me know.