SAving A copy of worksheat

Gokuba

New Member
Joined
Aug 6, 2018
Messages
19
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.

Code:
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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

pook_666

New Member
Joined
Aug 16, 2018
Messages
37
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.


Code:
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

New Member
Joined
Aug 6, 2018
Messages
19
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

New Member
Joined
Aug 16, 2018
Messages
37
.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>
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,109,005
Messages
5,526,233
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top