Replace formulas with values and save it as a new file and change original file back to formulas aft-CodePudding

I have an Excel-File in which the user can click on a button to save a version without formulas and only with values.
So far I use this VBA for it:

Sub Create_version_with_values_only()

Dim b As Worksheet
For Each b In Worksheets
b.Cells.Copy
b.Cells.Cells.PasteSpecial Paste:=xlPasteValues
Next b

Application.CutCopyMode = False
ActiveWorkbook.SaveCopyAs "G:\Folder\test.xlsm"
ThisWorkbook.Close SaveChanges:=False

End Sub

This VBA itself worsk fine.

However, the issue is that I have to close the file after the value-version of the file is created because the original version will not be available anymore.

Therefore, I am wondering if there is an alternative way to create the value-version of the file that makes it possible to go back to the original file afterwards.

Something like this:

Step 1) Change all formulas to values.
Step 2) Save the version with the values in the folder.
Step 3) Undo the value-replacements in original sheet without closing it.

Do you have any idea how to solve it?

CodePudding user response：

There might be a more simple way to get there, but here's how you'd create a new workbook, transfer the values over and save.

Public Sub SaveValues()
    Dim newWb As Workbook
    Set newWb = Workbooks.Add 'create a new workbook for the values
    
    Dim ws As Worksheet, newWs As Worksheet
    For Each ws In ThisWorkbook.Worksheets
    
        With newWb 'create worksheets and name them in new workbook
            If ws.Index = 1 Then
                Set newWs = .Worksheets(1)
            Else
                Set newWs = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
            End If
            newWs.Name = ws.Name
        End With
    
        With ws.UsedRange 'move values to new worksheet
            newWs.Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
        End With
    Next
    
    'save new workbook. If the current workbook is a .xlsb, change the .xlsm in the code below
    newWb.SaveAs Replace(ThisWorkbook.FullName, ".xlsm", "_hardcoded.xlsm"), xlOpenXMLWorkbookMacroEnabled
    newWb.Close
End Sub

Updated for alternatives below:

Alternative

An alternative is to use ThisWorkbook.Worksheets.Copy to copying all worksheets in one go. Unfortunately, to use this code, we have to use ActiveWorkbook to make a reference to the new workbook. (I hoped it might return a Workbook or Worksheets object)

Public Sub SaveValues2()
   Dim newWB As Workbook
   
   ThisWorkbook.Worksheets.Copy
   Set newWB = ActiveWorkbook 'not great practice
   
   Dim ws As Worksheet
   For Each ws In newWB.Worksheets
        With ws.UsedRange 'hardcode values
            .Value = .Value
        End With
    Next

    newWB.SaveAs Replace(ThisWorkbook.FullName, ".xlsm", "_hardcoded.xlsm"), xlOpenXMLWorkbookMacroEnabled
    newWB.Close
End Sub