I am getting an error when I am attempting to close a backup file I created in my Save and Close Module

[tewell53]

I am wanting to create a backup file to my backup directory the close that file. I then want to save and the close the current workbook. Then close the Excel Application. I am having trouble closing the backup file and at the end closing Excel.

'/-------------------------------------------------------------
'/ Main total worksheet(23-24)  save and close button
'/-------------------------------------------------------------


Sub SaveAndClose()
    Dim BUFileName As String                                                                     '/ Declaring file path name
    Dim MyName As String                                                                       '/ Declaring file name
    Current_date = Format(Now, "MMDDYY_hhmmss")                                                '/ Assigning date & Time to use in file name

'/---------------------------------------------------------------
'/ Turning off notifications for savings and closing
'/---------------------------------------------------------------
    Application.DisplayAlerts = False
    Application.EnableEvents = False
'/---------------------------------------------------------------
'/ Creating a backup file and then closing it
'/---------------------------------------------------------------
    MyName = ActiveWorkbook.Name                                                               '/ Assigning file name of current active workbook
    BUFileName = "G:\TECH\ib271t0o\MyBackups\" & MyName & "-" & Current_date & ".xlsm"           '/ Buliding full path name for backup file
    ActiveWorkbook.SaveAs FileName:=BUFileName                                                   '/ Saving backup file
    Workbooks("G:\TECH\ib271t0o\MyBackups\" & MyName & "-" & Current_date & ".xlsm" ).Close
'/---------------------------------------------------------------
'/ Turning off notifications for savings and closing
'/ Saving any changes
'/ closeing this workbook
'/---------------------------------------------------------------
   ThisWorkbook.Save
   Application.Quit
   Application.DisplayAlerts = True
   Application.EnableEvents = True
    
 End Sub

All help is greatly appreciated.

Terry

 

[dmt32]

Hi
untested but see if this update to your code does what you want

'/-------------------------------------------------------------
'/ Main total worksheet(23-24)  save and close button
'/-------------------------------------------------------------


Sub SaveAndClose()
    Dim BUFileName  As String        '/ Declaring file path name
    Dim MyName      As String, Current_Date As String        '/ Declaring file name variables
    
    Current_Date = Format(Now, "MMDDYY_hhmmss")        '/ Assigning date & Time to use in file name
    
    On Error GoTo myerror:
    '/---------------------------------------------------------------
    '/ Turning off notifications for savings
    '/---------------------------------------------------------------
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    
    ThisWorkbook.Save
    '/---------------------------------------------------------------
    '/ Creating a backup file
    '/---------------------------------------------------------------
    MyName = ThisWorkbook.Name        '/ Assigning file name of current active workbook
    BUFileName = "G:\TECH\ib271t0o\MyBackups\" & MyName & "-" & Current_Date        '/ Buliding full path name for backup file
    ThisWorkbook.SaveCopyAs Filename:=BUFileName        '/ Saving backup file
    
    '/---------------------------------------------------------------
    '/ Turning on notifications and closing
    '/---------------------------------------------------------------
    
myerror:
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    
    If Err <> 0 Then
        MsgBox (Error(Err)), 48, "Error"
     Else
        ThisWorkbook.Close False
  End If
    
    'Personally, I WOULD NOT recommend that you do this.
    'Application.Quit
    
End Sub

Dave