Save a copy but keep original file open with same name.

[TDPorter]

Okay so I want to save a copy of my workbook with the file name being a combination of cells (AB8, AC8, AD8). The 1st cell used for the file name consists of the =Today() function, I think this might pose a problem. The other two cells are just text, no biggie. I also want to make it so the workbook doesn't change name as with the Save_As function. I want its file name to remain but save a copy under a different name using the cells. It would be nice if it would save the original just after creating the copy under a different name. Any suggestions?

 

[wigi]

For instance:

With ActiveWorkbook
   .SaveCopyAs .Path & "\" & Format(Date, "yyyymmdd") & "-" & [AC8] & "-" & [AD8] & ".xlsm" 
End With

 

[Jonmo1]

I assume you are referring to a VBA method,

Try the SaveCopyAs method..


As far as the Today() function in the filename, you'll need to format it to not use / in the date. As / are not allowed in filenames..

Try something like

FORMAT(Range("AB8"),"mm-dd-yyyy")

That will use - instead of /


Hope that helps.

 

[Richard Schollar]

Hi

You need to use VBA for this - you have the handy SaveCopyAs function:

With Sheets("Whatever")
  ThisWorkbook.SaveCopyAs Format(.Range("AB8").Value,"yyyy-mm-dd") & .Range("AC8").Value & .Range("AD8").Value
End With

 

[TDPorter]

I believe it's working but how can I define the path that is will be saved at, I can't seem to find the files after running the macro, not on desktop or in MyDocuments. Almost there! Thank you for getting me this far....

 

[wigi]

I believe it's working but how can I define the path that is will be saved at, I can't seem to find the files after running the macro, not on desktop or in MyDocuments. Almost there! Thank you for getting me this far....

If you had looked at my suggestion in detail, you would have noticed .Path in there. So it will be stored in the same directory as the ActiveWorkbook. (which is saved as a copy).