Save as using multiple Cells

[mille1sj]

Hi Everyone,

I have created a form that all of our project managers will be using each month to report data. I would like to know if anyone knows of a way to create a standard file name based on specific cells. I want all of the files to be saved in the following format Name_Project_ProjectNumber_Date.xlsm

the information is in cells B5_B4_L3_R3

Thanks, everyone has been very helpful with my past questions.

[dilipandey]

Hi mille1sj,


I see a VBA (macro) solution to this query... would that be fine with project managers or they want to save it manually every time ?

Regards,
DILIPandey

<click on below 'star' if this helps>

[mille1sj]

I think a macro would be fine, it would help to standardize the file names.

[dilipandey]

Hi mille1sj,

Enter following code in the workbooks for each manager and edit the Note in the code:-

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.DisplayAlerts = False
Application.ScreenUpdating = False
'Note:- here I am using cell IV1 for some combining texts for workbook name, if required choose other cell
Range("iv1").Formula = "=b5&""_""&b4&""_""&l3&""_""&TEXT(R3,""dd-mmm-yy"")"
Range("iv1").Select
i = ActiveCell.Value
ChDir "C:\Users\DILIPandey\Desktop" 'Note:- change the path as per your preference
ActiveWorkbook.SaveAs Filename:=i, FileFormat:=xlOpenXMLWorkbookMacroEnabled  

'Note: remove code line FileFormat ...Enabled portion from above line if you face any issue with Excel 2003.

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

How to add:-
open workbook, press Alt + F11, locate your workbook in left window, double click on "This workbook" and paste above code

Regards,
DILIPandey

<click on below 'star' if this helps>

[mille1sj]

It works great! is ther a way to show the Save As box? I would like to show this in the event that they need to change the location slightly or change the file name?

[dilipandey]

you are welcome mille1sj,

to change the path, they can edit following part of the code:-
ChDir "C:\Users\DILIPandey\Desktop" 'Note:- change the path as per your preference

Else,
they can remove the above line as well

Regards,
DILIPandey

<click on below 'star' if this helps>