How to write VBA code to make backup copy in another folder

[MCC Wong]

I have created a shared bank book namely HSBC in J drive and when I save this
file, I would like to make a backup copy in C drive namely 'Backup HSBC', but
I don't know exactly how to write VBA code.

Should I use the macro function or the Visual Basic Editor under Tools??
Please teach me how to write the macro or VBA code!!

I've tried the following but won't work:

Sub HSBC (ByValSaveASUI As Boolean, Cancel As Boolean)
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs "C:\Backup HSBC.xls" & ActiveWorkbook.Name
ActiveWorkbook.Save
Application.DisplayAlerts = True
End Sub

What have I done wrong?? please advice!!

 

[Joel]

first, a macro that is called from a worksheet cannot havve any parameters.
I don't know if you are calling this macro from a worksheet or another macro.
It looks like (because your have parameters) that it is being called from
another macro

second, you file names is wrong. Do you have a folder called "Backup
HSBC.xls"? You will get an error indicating PATH not found itf you don't
have this folder.

the must be a path seperator "\" between a folder and the file name
from
ActiveWorkbook.SaveCopyAs "C:\Backup HSBC.xls" & ActiveWorkbook.Name
to
ActiveWorkbook.SaveCopyAs "C:\Backup HSBC.xls\" & ActiveWorkbook.Name

 

[Gord Dibben]

See response at one of your other postings.


Gord Dibben MS Excel MVP

 

[MCC Wong]

Dear Joel,

I'm writing the macro in a worksheet, so what should I do?

MCC