After save - Creating 2 sheets Copy without Formulas in New workbook and Save as

Question

I have a large Excel file (ORIGINAL.xlsm) with many sheets. I need to create automaticaly another file (COPY.xlsx), when I save the ORIGINAL.xlsm. The COPY.xlsx file should only contain 2 sheets (EXP1 and EXP2). But EXP1 and EXP2 must only contain values without formulas and ORIGINAL.xlsm may not be affected by this operations. I am trying to solve, but the original file is always negatively affected (formulas removed). I would like to avoid the use of ActiveWorkBook call because it is often operated with multiple open Excel.

    Option Explicit
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Dim UserProfile As String
Dim Path As String
Dim NewBook2 As Workbook
UserProfile = VBA.Environ$("Userprofile")

Path = UserProfile & "\Dropbox\Program\COPY.xlsx"


Set NewBook2 = Workbooks.Add
NewBook2.SaveAs Filename:=Path

ThisWorkbook.Sheets("EXP1").Copy Before:=Workbooks("COPY.xlsx").Sheets(1)
ThisWorkbook.Sheets("EXP2").Copy Before:=Workbooks("COPY.xlsx").Sheets(2)

Workbooks("COPY.xlsx").Sheets("EXP2").Activate
Workbooks("COPY.xlsx").Sheets("EXP2").UsedRange.Select


Workbooks("COPY.xlsx").Sheets("EXP2").Copy Before:=Workbooks("COPY.xlsx").Sheets(2)

REM PROBLEM HERE
Workbooks("COPY.xlsx").UsedRange.Value = Workbooks("COPY.xlsx").UsedRange.Value

End Sub

Answer 1

You neither need the line with .Activate nor the line with .Select to make this code work. You might benefit from reading How to avoid using Select in Excel VBA.

Note that your Workbooks("COPY.xlsx") is already set to NewBook2 so better use that variable just in case the name COPY.xlsx ever changes NewBook2 will always work.

Option Explicit

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    Dim UserProfile As String
    UserProfile = VBA.Environ$("Userprofile")

    Dim Path As String
    Path = UserProfile & "\Dropbox\Program\COPY.xlsx"
    
    Dim NewBook2 As Workbook
    Set NewBook2 = Workbooks.Add
    NewBook2.SaveAs Filename:=Path
    
    ThisWorkbook.Sheets("EXP1").Copy Before:=NewBook2.Sheets(1)
    ThisWorkbook.Sheets("EXP2").Copy Before:=NewBook2.Sheets(2)

    NewBook2.Sheets(2).UsedRange.Value = NewBook2.Sheets(2).UsedRange.Value

    'here you need to save and probably close your new workbook
    NewBook2.Close SaveChanges:=True 'or NewBook2.Save if you want to keep it open.
End Sub

The issue you had was that a workbook does not have a UsedRange like Workbooks("COPY.xlsx").UsedRange you need to specify a worksheet like NewBook2.Sheets(2).UsedRange.