I need to create a Save As macro, activated by a button, that will take
some information that is already contained in specfic cells of a
workbook to create a specific file name. Here is an example of the
file name:

Smith (ABCD) BB abc 3.11.05 where...

Smith is last name contained in cell A1
ABCD is the employee ID which will need to be entered manually
BB is a project code contained in cell B2
abc is auditors initinals to be entered manually
3.11.05 is the date the file was saved


--
BigDave
------------------------------------------------------------------------
BigDave's Profile: http://www.excelforum.com/member.php...fo&userid=7741
View this thread: http://www.excelforum.com/showthread...hreadid=375742

Hi Dave!

This macro should work for you...

Code:
--------------------
Sub saveFile()
Dim lastName, employeeID, projCode, auditorID As String
Dim saveDate, newFileName As String
'Prompt user for ID & initials...
employeeID = InputBox("Enter employee ID: ")
auditorID = InputBox("Enter Auditor initials: ")
' Get last name...
lastName = ActiveSheet.Range("A1").Value
' Get project code...
projCode = ActiveSheet.Range("B2").Value
' If all variables are present, create new file name based on variables and save it w/new name...
If employeeID < "" And auditorID < "" And lastName < "" And projCode < "" Then
saveDate = CStr(Format(Now(), "m.d.yy"))
newFileName = lastName & " (" & employeeID & ") " & projCode & " " & auditorID & " " & saveDate & ".xls"
' Save file as new file name...
ActiveWorkbook.SaveAs Filename:=newFileName, FileFormat:=xlNormal
Else
' Otherwise, data is missing for new file name so alert user...
MsgBox ("Can't save file; required data is missing.")
End If
End Sub
--------------------

Hope this helps,
theDude


--
theDude
------------------------------------------------------------------------
theDude's Profile: http://www.excelforum.com/member.php...o&userid=16550
View this thread: http://www.excelforum.com/showthread...hreadid=375742