title: VBA Macro to save an excel file to a different backup location
body: <p>I am trying to create a Macro that either runs on close or on save to backup the file to a different location.<br>
  At the moment the Macro I have used is:  </p>
  
  <pre><code>Private Sub Workbook_BeforeClose(Cancel As Boolean)  
  'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)  
      'Saves the current file to a backup folder and the default folder  
      'Note that any backup is overwritten  
      Application.DisplayAlerts = False  
      ActiveWorkbook.SaveCopyAs Filename:="T:\TEC_SERV\Backup file folder - DO NOT DELETE\" &amp; _ 
      ActiveWorkbook.Name  
      ActiveWorkbook.Save  
      Application.DisplayAlerts = True  
  End Sub  
  </code></pre>
  
  <p>This creates a backup of the file ok the first time, however if this is tried again I get:  </p>
  
  <blockquote>
    <p>Run-Time Error '1004';<br>
    Microsoft Office Excel cannot access the file 'T:\TEC_SERV\Backup file folder - DO NOT DELETE\Test Macro Sheet.xlsm. There are several possible reasons:<br>
    The file name or path does not exist<br>
    The file is being used by another program<br>
    The workbook you are trying to save has the same name as a...</p>
  </blockquote>
  
  <p>I know the path is correct, I also know that the file is not open anywhere else. The workbook has the same name as the one I'm trying to save over but it should just overwrite.</p>
  
  <p>Any help would be much appreciated.</p>