Backup Macro Enabled VBA

[Shazz]

Can anyone help me, I have the two below codes, but I do not know how to put them together to work to save a backup of a Macro Enabled Workbook,

At present I am using the 1st code but it only saves as a normal workbook and all my macros will not work.

All I want is to be able to press a button and a backup of the file is saved in the specific location.

Private Sub CommandButton1_Click() ' if you need to get FileName somewhere out
Dim Fname As String
Fname = "2017 Holiday Register Backup"  'change filename here
ActiveSheet.Copy
With ActiveWorkbook
' change adress
    .SaveAs Filename:="Z:\STAFF HOLIDAY CHARTS\Archived & Backups\" & Fname
    .Close
End With
End Sub

Dim sExt as String
  'Determine FileFormat to SaveAs
  Select Case ThisWorkbook.FileFormat
  Case 56: sExt = ".xls"    'xlExcel8
  Case 52:  sExt = ".xlsm"  'xlOpenXMLWorkbookMacroEnabled
  Case 50:  sExt = "xlsb"  'xlExcel12
  Case Else
    MsgBox "Unable to determine file format; exiting...", vbInformation, vbNullString
    Exit Sub
  End Select

This is the instruction I have been given, but I have no idea how to make this work.

Put that code after Dim fName as String
and add highlighted part in old code.
.SaveAs Filename:="C:\users\sergej" & Fname & sExt

If anyone can give me an exact code or a better way of doing what I want, I would be very grateful.

Shazz
xx

 

[Shazz]

If the code can be adapted to create a dated backup, that would be even better.

Summary of Requirements

Dated backup to save Macro Enabled Workbook 2017 Holiday Register Backup to be save to Z:\STAFF HOLIDAY CHARTS\Archived & Backups

Shazz
xx

 

[vletm]

If need to make 'backup' then You could use 'SaveCopyAs' like this
'just' change .Path to Your backup-path ...

Sub Do_Save_As()
    Application.ScreenUpdating = False
    With ThisWorkbook
        Fpath = .Path       '   change .Path as You want
        If Right(Fpath, 1) <> Application.PathSeparator Then Fpath = Fpath & Application.PathSeparator
        .SaveCopyAs Fpath & .Name, FileFormat:=.FileFormat
    End With
    Application.ScreenUpdating = True
End Sub

 

[Shazz]

Hi, I have changed to the below and nothing is happening??

Sub Do_Save_As()
    Application.ScreenUpdating = False
    With ThisWorkbook
        Fpath = Z:\STAFF HOLIDAY CHARTS\Archived & Backups'  change .Path as You want
      If Right(Fpath, 1) <> Application.PathSeparator Then Fpath = Fpath & Application.PathSeparator
        .SaveCopyAs Fpath & .Name, FileFormat:=.FileFormat
    End With
    Application.ScreenUpdating = True
End Sub

 

[vletm]

Did You check that is there that file
in 'Z:\STAFF HOLIDAY CHARTS\Archived & Backups'?

 

[Shazz]

Yes I copied it direct from the file path.

 

[vletm]

TWO choices
[time-mark = when file has saved]
A) Short version
1) check that 'backup-folder' file's time-mark
2) Run that Macro
3) compare backup-folder' file's time-mark

B) Longer version
1) You have ORIGINAL file 'somewhere'
2) Delete that 'backup-folders' file (It ALWAYS copies OVER old file)
3) Run that Macro
4) Check is there (backup-folder) copied file.

 

[Shazz]

Sorry, you have confused me here.

I do have an original file that is my master, I want to be able to click on a button in that master file and it will crreate a backup of the file with the date and save that backup to the location Z:\STAFF HOLIDAY CHARTS\Archived & Backups.

Shazz

 

[Shazz]

The master file is 2017 Holiday Register but I want it to save it as 2017 Holiday Register_BACKUP_DATE or DATE_BACKUP_2017 Holiday Register, that way it will file in date order.

 

[vletm]

That Macro should make backup to that Your named folder, Okay?
If there is ALREADY same named file it will OVERWRITE old file, Okay?
...
Now You would like to change name of file too ...
which date format?

 

[Shazz]

dd/mm/yyyy please.

Thanks

Shazz
x

 

[vletm]

Not possible date format!

Sub Do_Save_As()
    Application.ScreenUpdating = False
    With ThisWorkbook
        Fpath = .Path   '   change .Path as You want
        If Right(Fpath, 1) <> Application.PathSeparator Then Fpath = Fpath & Application.PathSeparator
        Fname = Format(Date, "yyyymmyy") & "_" & .Name
        .SaveCopyAs Fpath & Fname
    End With
    Application.ScreenUpdating = True
End Sub

 

[Shazz]

vletm,

This code is not working, I press the button and it does nothing?? path = .Z:\STAFF HOLIDAY CHARTS\Archived & Backups ' shows as an error, the path name is correct though??

Sub Do_Save_As()
    Application.ScreenUpdating = False
    With ThisWorkbook
        Fpath = .Z:\STAFF HOLIDAY CHARTS\Archived & Backups  '  change .Path as You want
      If Right(Fpath, 1) <> Application.PathSeparator Then Fpath = Fpath & Application.PathSeparator
        Fname = Format(Date, "yyyymmyy") & "_" & .Name
        .SaveCopyAs Fpath & Fname
    End With
    Application.ScreenUpdating = True
End Sub

 

[vletm]

OMG!
If Your 4th line is really like
Fpath = .Z:\STAFF HOLIDAY CHARTS\Archived & Backups
then it really cannot work!
Fpath = "Z:\STAFF HOLIDAY CHARTS\Archived & Backups\"
... and compare to Your original code how to write!
Normal case it won't give any message ... just 'SaveCopyAs'.

 

[Shazz]

There really is no need to be so rude, I am not a code writer which is why I have come on here for advise.

The code does not work even with your alteration above.

 

[vletm]

Upload a Sample File to get a quicker response

 

[Shazz]

I have had to delete a lot of information from the workbook to get it to under 1mb, please see attached, it is the Backup Button bottom right that I want to press to do the weekly backup and save to the specific location.

 

[vletm]

Okay ...
Your 'Weekly Backup'-button is not Assign any Macro!
... or it "wait for default 'CommandButton1_Click'-Macro".
Do as below ...
1) Select 'Weekly Backup'-button


2) Find from list 'Sheet21.Do_Save_As -macro


3) Select it and press [OK]

4) Now Your button could work and run that Macro - test it.

(I cannot do this for You, because You have used 'ActiveX-components'.)

 

[Shazz]

Thankyou, that now works perfectly.

I was not aware that I had to assign the macro, I though writing the code was enough, apologies.

Thank you very much for your help.