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

A WorkBook has to `UsedRange`-property, only a WorkSheet. – FunThomas May 05 '21 at 12:29 — FunThomas, May 05 '21 at 12:29
YES, this was a problem :-D – excel222 May 05 '21 at 12:59 — excel222, May 05 '21 at 12:59

Pᴇʜ · Accepted Answer · 2021-05-05T14:15:15.950

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.

score 0 · Answer 2 · answered May 05 '21 at 13:12

This works for me... Thank you I'm not too confident relative links in Excel as ActiveWorkbook or NewBook2.Sheets(2) For this reason, open file names are in my code. This best proved to work with more open Excel (for me).

        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:=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").UsedRange.Value = Workbooks("COPY.xlsx").Sheets("EXP2").UsedRange.Value
  
rem if file exists no alert display  
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Workbooks("COPY.xlsx").SaveAs Filename:=path
    Workbooks("COPY.xlsx").Close
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    End Sub

Note that `NewBook2` is an absolute reference to the new added workbook and no relative reference like `ActiveWorkbook`. Is is a good practice not to repeat the file name over and over like `Workbooks("COPY.xlsx")` and use a variable to reference it like `NewBook2`. Also you don't need to `activate` or `select` anything to make it work (that is a bad practice). See my anwser for how your code would look like following a good practice. — Pᴇʜ, May 05 '21 at 13:16
Variable NewBook2 is interesting I'll try to use it... But in your solution is 3 sheets in COPY.xlsx file — excel222, May 05 '21 at 13:41
I edited my answer, now it has only 2 sheets. That 3rd sheet came from your original code, so I thought it was there in purpose. Have a look. — Pᴇʜ, May 05 '21 at 14:16

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

2 Answers2