How to save a new excel file with edits without changing original?-CodePudding

In some prior version of excel I used to be able to make some edits in a workbook (call it workbook A), choose SaveAs, rename the file (call it workbook B), and when it's done, I could open my original workbook A and find it in a state of pre-edits while workbook B saved with edits. For reference I'm using Excel for Microsoft 365.

anyway, that's what I'm trying to do with the code below and I run into one problem. It saves workbook A with the same exact edits that are in workbook B whereas I want to keep workbook A unchanged.

Sub NoticeGenerator()
    Dim wxhS As Worksheet, wbkT As Workbook
    Const TABCOLOR As Long = 192 'Standard Tab color Dark Red
    
 
 'Hides any tabs that are Dark Red
   For Each wxhS In Application.ActiveWorkbook.Worksheets
        If wxhS.Tab.Color <> TABCOLOR Then
            wxhS.Visible = xlSheetHidden
            
        End If
        If wxhS.Tab.Color = TABCOLOR Then
            wxhS.Cells.Font.Color = RGB(0, 0, 0)
            
        End If
    
    Next      
  
'Displaying the saveas dialog box
FName = Application.GetSaveAsFilename("Notice Generator v", _
    "Excel files,*.xlsm", 1, "Select your folder and filename")

'Saves file if filename is entered, otherwise it won't save
If FName <> False Then
ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
     End If


 For Each wxhS In Application.ActiveWorkbook.Worksheets
        If wxhS.Tab.Color = TABCOLOR Then
            wxhS.EnableSelection = xlUnlockedCells
            wxhS.Protect
        End If
 Next



End Sub

CodePudding user response：

Turning off AutoSave - this is the answer. It makes the "Save As" work both manually and in code. It actually changes what options you have for saving. With AutoSave on, you only get "Save a Copy" but with it off you get an option to "Save" and one to "Save As".

The final code I used below turns AutoSave off before moving on:

Sub NoticeGenerator()
    Dim wxhS As Worksheet, wbkT As Workbook
    Const TABCOLOR As Long = 192 'Standard Tab color Dark Red

'Turns off AutoSave because with it on it would compromise the original file while saving
    If Val(Application.Version) > 15 Then
        ThisWorkbook.AutoSaveOn = False
    End If   
 
 'Hides any tabs that are Dark Red
   For Each wxhS In Application.ActiveWorkbook.Worksheets
        If wxhS.Tab.Color <> TABCOLOR Then
            wxhS.Visible = xlSheetHidden
            
        End If
        If wxhS.Tab.Color = TABCOLOR Then
            wxhS.Cells.Font.Color = RGB(0, 0, 0)
            
        End If
    
    Next
          
'Displaying the SaveAs dialog box
FName = Application.GetSaveAsFilename("Notice Generator v", _
    "Excel files,*.xlsm", 1, "Select your folder and filename")

'Saves file if filename is entered, otherwise it won't save - only works if AutoSave is turned off first in code above
If FName <> False Then
ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
     End If

'Soft Protects without password red tabbed sheets to avoid compromising the file accidentally
 For Each wxhS In Application.ActiveWorkbook.Worksheets
        If wxhS.Tab.Color = TABCOLOR Then
            wxhS.EnableSelection = xlUnlockedCells
            wxhS.Protect
        End If
 Next


End Sub