Keep Source Workbook open after save as to another file

[hennedy]

Hi,
I have a workbook that is updated from an external source. The code works but it closes both the Source Workbook and the New Workbook. How do I edit my code to keep the Source Workbook open? Here is the code:
Private Sub Worksheet_Calculate()
Dim target As Range
Set target = Range("A2:Q2")
If Not Intersect(target, Range("A2:Q2")) Is Nothing Then
Dim path As String
Dim filename1 As String
path = "C:\Users\rafin\"
filename1 = Range("G2") & Range("A2").Text
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=path & filename1 & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.DisplayAlerts = True
ActiveWorkbook.Close

 

[6StringJazzer]

If you use SaveAs it works just as if you did that from the user interface. That mean it saves the file with a new name, and then that is the file that is open. (Your code doesn't close two files, because only one was open--the renamed file.)

Two options I can think of:

Re-open the original file after the SaveAs. You will have to save the name before SaveAs so you can re-open it.

Use SaveCopyAs instead of SaveAs. When you do this, the new copy will not be open so you don't have to close it. You cannot specify a file format with SaveCopyAs but it looks like you can keep the same format.

 

[hennedy]

Hello,
Thank you for your suggestions. I couldn't get it to work that way since it is a .xlsm. Tried out another option by adding a few lines to the bottom of my code. Here is the full code.

Private Sub Worksheet_Calculate()
Dim target As Range
Application.ScreenUpdating = False
Set target = Range("G2")
If Not Intersect(target, Range("G2")) Is Nothing Then
Dim path As String
Dim filename1 As String
path = "C:\Users\rafin\"
filename1 = Range("G2") & Range("A2").Text
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=path & filename1 & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.DisplayAlerts = True
Application.ScreenUpdating = True
ChDir "C:\Users\rafin"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\rafin\Rafina code test2 4-1.xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False