Hi
Can someone please help me with this?
I have a macro listed below, all the maco does is save a back up of the file i am saving just incase the file gets overwritten by mistake.
At the moment the back up file saves to the same directory as the workbook being saved, i would like it to save to a folder in the same directory called "Back Up" but i am having problems doing this. I do not want to put in the C:\Directory\Filename all i want to do is save to the folder "Back Up" in the same directory as the orig file no matter where it is.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim OriginalName As String
Dim BackupName As String
OriginalName = ThisWorkbook.FullName
BackupName = Left(OriginalName, Len(OriginalName) - 4) & ".bak" & _
Right(OriginalName, 4)
Application.EnableEvents = False
Application.DisplayAlerts = False ' no "file exists" please
ActiveWorkbook.SaveAs Filename:=BackupName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.SaveAs Filename:=OriginalName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True ' turn them back on
Application.EnableEvents = True ' and these also
Cancel = True ' we did it once, why do it again
End Sub
Bookmarks