Excel SaveAs changes active workbook - How to SaveAs and keep active workbook active?

Question

In an MS-Excel 2007 macro, I want to save the the workbook (which contains the macro) to another workbook before beginning updates. However, when I execute the SaveAs, the current workbook becomes the saved workbook and not the original one I stared with...

Say I start with workbook "A.xlsm", within workbook "A.xlsm" I issue command

With ActiveWorkbook
          .SaveAs FileName:=ThisWorkbook.Path + "\" & "B.xlsm"
          Close 0
End With

Any changes in the active workbook appears in workbook "B.xlsm" not "A.xlsm"!!!

Question Within workbook "A.xlsm", how can I save a copy without changing the active workbook?

[Another Way](http://www.siddharthrout.com/2011/09/02/using-save-as-and-keeping-original-file-and-new-file-open-at-the-same-time/) — Siddharth Rout, Aug 02 '16 at 07:37
@SiddharthRout That post has been put 'under renovation'. Here's a Wayback Machine link to the original article while he's updating it: https://web.archive.org/web/20131213230603/http://www.siddharthrout.com/2011/09/02/using-save-as-and-keeping-original-file-and-new-file-open-at-the-same-time/ — Austin Gordon, Feb 01 '19 at 14:34
@AustinGordon [Updated Link](http://www.siddharthrout.com/index.php/2019/01/12/using-save-as-and-keeping-original-file-and-new-file-open-at-the-same-time-2/) — Siddharth Rout, Feb 02 '19 at 16:47

score 3 · Accepted Answer · 2016-08-03T15:40:09.303

3

SaveCopyAs saves a copy of the workbook without changing any references.

ActiveWorkbook.SaveCopyAs FileName:=ActiveWorkbook.Path + "\" & "B.xlsm"

Sub TestSaveAs()
    Dim xlWB As Workbook
    Set xlWB = ActiveWorkbook

    ActiveWorkbook.SaveCopyAs Filename:=ActiveWorkbook.Path + "\" & "B.xlsm"

    Debug.Print "ActiveWorkbook.FullName: " & ActiveWorkbook.FullName
    Debug.Print "xlWB.FullName: " & xlWB.FullName

End Sub

edited Aug 03 '16 at 15:40

answered Aug 02 '16 at 06:28

My understanding is that automatically changes the active workbook to "Thisworkbook". – NoChance Aug 03 '16 at 15:28
1

@NoChance I had a typo in my answer. I included a test to show that SaveCopyAs doesn't change any references. – Aug 03 '16 at 15:41
Excellent point thank you. Congratulations on knowing such a difference! – NoChance Aug 03 '16 at 15:46
Thanks for the check mark! – Aug 03 '16 at 15:52
Well deserved. I think SaveAs in this context, is simply harmful...It is as harmful as SQL DELETE FROM TableX, which does not ask for a condition! – NoChance Aug 03 '16 at 15:59

score 1 · Answer 2 · answered Aug 02 '16 at 06:17

1

Create a Workbook variable and assign ActiveWorkbook to it before saving

Dim xlWB as Workbook
Set xlWB = ActiveWorkbook
xlWB.SaveAs FileName:=ThisWorkbook.Path & "/" & "B.xlsm"

Then you can refer to xlWB instead of ActiveWorkbook, which always stays the same Workbook

answered Aug 02 '16 at 06:17

gizlmo

1,882
1
14
14

I think this would work. I assume that I could then do xlWB.Activate to make it the active workbook again. I will test and accept this as an answer if it works. Thx. – NoChance Aug 03 '16 at 15:27
yes you can, or just replace the `ActiveWorkbook` wherever you need it with the `xlWB`. Then you don't need to use `Activate` to access it – gizlmo Aug 04 '16 at 05:15

Excel SaveAs changes active workbook - How to SaveAs and keep active workbook active?

2 Answers2