You want VBA to write several copies of the current Excel workbook. SaveAs causes problems, because the original workbook is no longer open
Instead use .SaveAsCopy to save a copy of the workbook
As far as I can tell, SaveAsCopy does not exist in the Excel interface.
It allows you to keep the current workbook open with the original name and path, but save extra copies with a new name.
This is particularly handy if you need to remove macros from a workbook while saving.
Download the code from the video at the link below.
To download this workbook: https://www.mrexcel.com/download-center/2018/06/save-as-keeping-original-open.xlsm
List of upcoming seminars: Excel Seminar Schedule
Instead use .SaveAsCopy to save a copy of the workbook
As far as I can tell, SaveAsCopy does not exist in the Excel interface.
It allows you to keep the current workbook open with the original name and path, but save extra copies with a new name.
This is particularly handy if you need to remove macros from a workbook while saving.
Download the code from the video at the link below.
To download this workbook: https://www.mrexcel.com/download-center/2018/06/save-as-keeping-original-open.xlsm
List of upcoming seminars: Excel Seminar Schedule
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2213: Save As Using VBA, But Keep the Original Open.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, you might have noticed that I've been offering the download of each podcast recently because a lot of people have been asking me for that, and so I was trying to make life as easy as possible.
And the whole goal was to save a copy that you can download, but I didn't want the extra stuff-- you know, the stuff that's for my own internal use-- there, so I wanted to get rid of that.
And, you know, let's say that I had a situation where I had to write 12 workbooks, right?
Each with a different product.
So, I'm going to loop through these products and I'm going to write it there to A2 and then save the workbook, and maybe clean up some stuff.
Alright.
So my first pass through here is a macro like this, alright?
So, we define the current workbook-- Worksheets("Data), Worksheets("Report") to find those-- and then figure out how many rows of data we have today, we're going to loop from Row 2 down to the final row, copy the product from the data workbook over to the report workbook.
Alright, and now here's where I'm about to get into trouble.
So the new workbook is going to be called "C:\aaa\" and then Apple.xlsx, and I'm going to Save As a, you know, with Apple.xlsx, and change to XML-- open xml workbook-- which will strip the macros out.
Alright.
But now I'd like to close that workbook, but unfortunately when you do a Save As-- see right now, I'm in podcast 2013-- when I do a Save As after that point in the code, I'm no longer going to be in Podcast 2013; I'm going to be in Apple.xlsx.
Alright?
So, now, if I want to start deleting stuff, I'm going to be deleting it in the copy, but when I close the copy, well, I can't get back to the original file.
Alright?
And this macro-- actually, my head's about to explode trying to figure out if the loop will still work or not work, right?
So I think Save As is the wrong way to go here.
Well, actually, hang on.
We could go two routes: First, I could have another workbook that opens Podcast 2213, does the stuff and then Saves As with the new name, or I'm going to go this way, alright, and this is the method I ended up using-- alright, and we're going to define this workbook, but then also a new workbook.
Right.
And everything is kind of the same down here until we get to the point where I was about to do WBT.SaveAs.
Check this out: SaveCopyAs-- now, this doesn't exist, as far as I can tell, in regular Excel...
this is VBA only.
SaveCopyAs says, "Hey, look we're in a file called 2213 and I want you to take that file 2213 in its current state, and save it to disk, and close.” Keep the original file open-- 2213 stays open-- but now we have a brand new file on disk called Apple.xlsm.
Actually, at first, I'm just going to call it DeleteMe.xlsm.
Alright.
But it creates an identical copy and keeps the original file-- the file that the macro is running in-- open, and that's the important part, right?
So now that I have DeleteMe out there, I open it, assign it to WBN, do the things I need to do, get rid of all the extra sheets-- I know what I have.
Notice, before you delete the sheets, you want to do DisplayAlerts = False, otherwise it keeps asking you, "Hey, you're not going to get the sheet back." I get it.
And then, finally here, select the first worksheet FN is going to be Apple.xlsx, and then we can do WBN.SaveAs Apple, as an Open XMLWorkbook.
No macros.
And then Close-- the beautiful thing about Close is I'm now back in this workbook, 2213.
Alright, it's been working really, really well, and the key to this whole thing is SaveCopyAs-- SaveCopyAs.
So, to me-- well that's been around for a long time-- I never used it, and now I realize there's probably been a lot of times in the past where I should have used it.
And maybe, you know, you should be using it or consider using it as well.
Okay, I forgot to mention one thing: the important thing is, with Save As Copy-- Save As Copy-- so when I do the Save As Copy, if I try to change to an xslx file type, I cannot change the file type here just by changing the extension in NewFN and when trying to open that subsequent workbook, they'll detect that it has macros, and it has the wrong extension, and it will yell at you.
Right?
So, you have to save it as xlsm and then later come back, reopen it, and save it as xlsx.
But that all works with this macro.
So, hey, when I'm updating this book, Excel 2016, this summer, for Excel 2019, I'll make sure to include this tip.
I think it's a useful tip to SaveCopyAs.
Wrap-up from today: You want VBA to write several copies of the current workbook; Save As causes problems because the original workbook is no longer open; instead you use .SaveAsCopy to save a copy of the workbook.
If you want to download the workbook from today's video, including the macro, visit the URL in the YouTube description.
I want to you for stopping by, I'll see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, you might have noticed that I've been offering the download of each podcast recently because a lot of people have been asking me for that, and so I was trying to make life as easy as possible.
And the whole goal was to save a copy that you can download, but I didn't want the extra stuff-- you know, the stuff that's for my own internal use-- there, so I wanted to get rid of that.
And, you know, let's say that I had a situation where I had to write 12 workbooks, right?
Each with a different product.
So, I'm going to loop through these products and I'm going to write it there to A2 and then save the workbook, and maybe clean up some stuff.
Alright.
So my first pass through here is a macro like this, alright?
So, we define the current workbook-- Worksheets("Data), Worksheets("Report") to find those-- and then figure out how many rows of data we have today, we're going to loop from Row 2 down to the final row, copy the product from the data workbook over to the report workbook.
Alright, and now here's where I'm about to get into trouble.
So the new workbook is going to be called "C:\aaa\" and then Apple.xlsx, and I'm going to Save As a, you know, with Apple.xlsx, and change to XML-- open xml workbook-- which will strip the macros out.
Alright.
But now I'd like to close that workbook, but unfortunately when you do a Save As-- see right now, I'm in podcast 2013-- when I do a Save As after that point in the code, I'm no longer going to be in Podcast 2013; I'm going to be in Apple.xlsx.
Alright?
So, now, if I want to start deleting stuff, I'm going to be deleting it in the copy, but when I close the copy, well, I can't get back to the original file.
Alright?
And this macro-- actually, my head's about to explode trying to figure out if the loop will still work or not work, right?
So I think Save As is the wrong way to go here.
Well, actually, hang on.
We could go two routes: First, I could have another workbook that opens Podcast 2213, does the stuff and then Saves As with the new name, or I'm going to go this way, alright, and this is the method I ended up using-- alright, and we're going to define this workbook, but then also a new workbook.
Right.
And everything is kind of the same down here until we get to the point where I was about to do WBT.SaveAs.
Check this out: SaveCopyAs-- now, this doesn't exist, as far as I can tell, in regular Excel...
this is VBA only.
SaveCopyAs says, "Hey, look we're in a file called 2213 and I want you to take that file 2213 in its current state, and save it to disk, and close.” Keep the original file open-- 2213 stays open-- but now we have a brand new file on disk called Apple.xlsm.
Actually, at first, I'm just going to call it DeleteMe.xlsm.
Alright.
But it creates an identical copy and keeps the original file-- the file that the macro is running in-- open, and that's the important part, right?
So now that I have DeleteMe out there, I open it, assign it to WBN, do the things I need to do, get rid of all the extra sheets-- I know what I have.
Notice, before you delete the sheets, you want to do DisplayAlerts = False, otherwise it keeps asking you, "Hey, you're not going to get the sheet back." I get it.
And then, finally here, select the first worksheet FN is going to be Apple.xlsx, and then we can do WBN.SaveAs Apple, as an Open XMLWorkbook.
No macros.
And then Close-- the beautiful thing about Close is I'm now back in this workbook, 2213.
Alright, it's been working really, really well, and the key to this whole thing is SaveCopyAs-- SaveCopyAs.
So, to me-- well that's been around for a long time-- I never used it, and now I realize there's probably been a lot of times in the past where I should have used it.
And maybe, you know, you should be using it or consider using it as well.
Okay, I forgot to mention one thing: the important thing is, with Save As Copy-- Save As Copy-- so when I do the Save As Copy, if I try to change to an xslx file type, I cannot change the file type here just by changing the extension in NewFN and when trying to open that subsequent workbook, they'll detect that it has macros, and it has the wrong extension, and it will yell at you.
Right?
So, you have to save it as xlsm and then later come back, reopen it, and save it as xlsx.
But that all works with this macro.
So, hey, when I'm updating this book, Excel 2016, this summer, for Excel 2019, I'll make sure to include this tip.
I think it's a useful tip to SaveCopyAs.
Wrap-up from today: You want VBA to write several copies of the current workbook; Save As causes problems because the original workbook is no longer open; instead you use .SaveAsCopy to save a copy of the workbook.
If you want to download the workbook from today's video, including the macro, visit the URL in the YouTube description.
I want to you for stopping by, I'll see you next time for another netcast from MrExcel.
