SAving A copy of worksheat

[Gokuba]

HI,

I would like to save a copy with today's date to a specific folder,. Just a copy, I do not want it to open or prompt, The below code ,is not working anymore, what is wrong with it:

just a copy so I will not loose my everyday work.

Sub Save()


Dim dtDate As Date
dtDate = Date


Dim strFile As String
strFile = "C:\Users\KubaJakubowicz\Desktop\Biezace\analiza magazynu\FINALNY CHRONI\" & "Finalny " & Format(dtDate, "mm-dd-yyyy") & ".xlsm"


ActiveWorkbook.SaveCopyAs Filename:=strFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False


End Sub

 

[pook_666]

Not sure if the same wookbook is in the same folder as where you want it to save, but give the below a go. This will save a copy of the workbook in that designated folder.

Sub savecopy()
    Dim xlsmFile As String
    
'Define xlsm filename
    xlsmFile = ActiveWorkbook.Name & " Finalny " & Format(Date, "mm-dd-yyyy") & ".xlsm"
    
'Save copy
    ActiveWorkbook.SaveCopyAs Filename:=[COLOR=#574123]"C:\Users\KubaJakubowicz\Desktop\Biezace\analiza magazynu\FINALNY CHRONI\"[/COLOR] & xlsmFile
    
End Sub

 

[Gokuba]

HI, the file is in a different folder.

So your code works but it resulted in some weird file name (.xlsm) in the middle.
Still, when I amended it doesnt work again .


So to describe it in more deatil:

I have a excel file in a folder on desktop and I would like to save a copy of that file while changing its name (adding date and time) to a specific folder.

 

[pook_666]

.xlsm is a macro enabled workbook. If you remove the & ".xlsm" then should still work. If not, what is the error that occurs?

Alternatively you could create an automatic event that saves a copy of the file when you close it. You could put the below code in the "thisworkbook" project in the objects section:

<code>

Private Sub workbook_beforeclose(Cancel As Boolean)
        Dim msg As String
        Dim ans As Long
        Dim fname As String
        
'pop up message
        msg = "Would you like to make a backup of this file?"
        ans = MsgBox(msg, vbYesNo)
        
'if yes then define file name & save copy
        If ans = vbYes Then
            fname = "C:\Users\KubaJakubowicz\Desktop\Biezace\analiza magazynu\FINALNY CHRONI\" & "Finalny " & Format(Date, "mm-dd-yyyy")
            ThisWorkbook.SaveCopyAs fname
        End If
End Sub

This will ask you before closing the workbook whether you want to save a copy or not. If yes, will automatically save a copy, whether you save the actual workbook being used or not when the prompt of "do you want to save" comes up when exiting. This is also good if anyone else uses this workbook as well.

Hope this helps!
</code>