VBA Save Backup Copy of Workbook before changing original

[skydiver]

I am trying to create a macro that takes an open workbook, saves a copy of the file as "BACKUP-" in the same directory of the original then goes back to the original workbook and does the modifications I require. I have the backup file being created just fine only when I start to perform the code to modify the original workbook sheet, it is still working on the backup copy of the workbook. How to I prevent the SaveAs file method from taking focus and preventing the changes from being made to the original file?

Sub Week()
    Dim Sourcewb  As Workbook
    Dim Sourcewbname As String
    Dim Backupwb As Workbook
    Dim Workingpath As String
    Dim BackupfName As String
    Dim TempBackupfName As String
    
    Set Backupwb = ActiveWorkbook
    Set Sourcewb = ActiveWorkbook
    
    Workingpath = Destinationwb.Path & "\"
    TempBackupfName = Destinationwb.Name
    BackupfName = Workingpath & "BACKUP-" & TempBackupfName
    With Destinationwb
        .SaveAs Filename:=BackupfName, _
        FileFormat:=52, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    End With
  
    Sourcewb.Sheets("Inventory").Select
    ActiveWindow.SmallScroll ToRight:=4
    
    Range("n16:n276").Select
    Selection.Copy
    Range("o16").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 2
    Range("g16:g276").Select
    Selection.ClearContents
    
End Sub

 

[Warship]

Maybe something like:

Windows("yourOriginalWorkbookName").Activate

After the SaveAs

 

[John Davis]

I am trying to create a macro that takes an open workbook, saves a copy of the file as "BACKUP-" in the same directory of the original then goes back to the original workbook and does the modifications I require. I have the backup file being created just fine only when I start to perform the code to modify the original workbook sheet, it is still working on the backup copy of the workbook. How to I prevent the SaveAs file method from taking focus and preventing the changes from being made to the original file?

Sub Week()
    Dim Sourcewb  As Workbook
    Dim Sourcewbname As String
    Dim Backupwb As Workbook
    Dim Workingpath As String
    Dim BackupfName As String
    Dim TempBackupfName As String
    
    Set Backupwb = ActiveWorkbook
    Set Sourcewb = ActiveWorkbook
    
    Workingpath = Destinationwb.Path & "\"
    TempBackupfName = Destinationwb.Name
    BackupfName = Workingpath & "BACKUP-" & TempBackupfName
    With Destinationwb
        .SaveAs Filename:=BackupfName, _
        FileFormat:=52, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    End With
  
    Sourcewb.Sheets("Inventory").Select
    ActiveWindow.SmallScroll ToRight:=4
    
    Range("n16:n276").Select
    Selection.Copy
    Range("o16").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 2
    Range("g16:g276").Select
    Selection.ClearContents
    
End Sub

Does adding this change help?

    With Destinationwb
        .SaveAs Filename:=BackupfName, _
        FileFormat:=52, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    End With
    
    ActiveWorkbook.SaveAs Filename:=TempBackupfName, _
        FileFormat:=52, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

 

[skydiver]

I have temporarily forced the save into the same folder without user intervention with this code:

 Dim Destinationwb As Workbook
    Dim Workingpath As String
    Dim BackupfName As String
    Dim TempBackupfName As String
   
    Set Destinationwb = ActiveWorkbook
        
    Workingpath = Destinationwb.Path & "\"
    TempBackupfName = Destinationwb.Name
    BackupfName = Workingpath & "BACKUP ResetWeek-" & TempBackupfName
    Destinationwb.SaveCopyAs Filename:=BackupfName
            
    Sheets("Inventory").Select
    ActiveWindow.SmallScroll ToRight:=4
    
    Range("n16:n276").Select
    Selection.Copy
    Range("o16").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 2
    Range("g16:g276").Select
    Selection.ClearContents
    
End Sub

The two limitations with my workaround code are
- If I have the original file version <12 xls files with macros I want to save as a 2007 version with macros enabled (xlsm)
- I want to offer the option to change the save to folder by opening the save as dialog in the current working directoy an pre-populating the filename


I will try the two suggestions to see if they work for me and report the results back.