Microsoft Excel - VBA code to save a copy as - Asked By pete r. on 30-Jan-14 08:54 AM

Hello

The 'Save As' function in Excel saves a copy of the file and the user then works on the new file.

I want to be able to save a copy of the file to a specific location but the user continue to work on the current file - I thought this could be done using code and have had an attempt at writing one but with no success and can't seem to find anything like it elsewhere on the internet.  Can anyone help?

Many thanks
Peter
Harry Boughen replied to pete r. on 30-Jan-14 08:05 PM
Hi Pete,
I am a bit perplexed as to why you would want to do this.
If you want to get back to your original file than you would have to close the newly named version and re-open the original.  But if you had data entry or other workings that you wanted to continue with then you would have had to save the original before-hand so why not continue in the renamed version?
So if you can be a bit more specific as to the reason and what you are trying to achieve it might be possible to be of further help.
Regards
Harry
Harry Boughen replied to pete r. on 30-Jan-14 09:16 PM
Hi Pete,
Found this somewhere else.  Haven't tried it.  Obviously would need to go into your VBA code module in the appropriate place.

ActiveWorkbook.SaveCopyAs "C:\test.xls"

Let me know how it goes.

Harry
pete r. replied to Harry Boughen on 01-Feb-14 11:15 AM
Harry

That works, many thanks - do you know what code I need to bring dialog box up for user to rename it themselves? (or alternatively to name the file with text from a certain cell in the spreadsheet). 

Aim is to make the spreadsheet as user friendly as possible without having to open and close files it is easier to simply create a copy - then have a code to clear the spreadsheet ready for the new month but with certain bits of data from previous month retained.

Peter
Harry Boughen replied to pete r. on 01-Feb-14 02:42 PM
Hi Pete
Something like

Dim strFileName As String

strFileName = Range("A1").Value
ActiveWorkbook.SaveCopyAs strFileName

The cell address will need to be changed to suit and the contents of the cell should include the path if you are saving to a different directory.
Regards
Harry