I wasn't sure where you wanted the information from H2 in the workbook name,
so I stuck it in just ahead of the date. Here's the final code. Same
instructions as before - but if you already put code in the workbook, just
delete the old and copy, paste and edit the new.

Private Sub Workbook_BeforeSave(ByVal _
SaveAsUI As Boolean, Cancel As Boolean)
'change this path to the path you want it to end up in
'Const savePath = "C:\"
'for example you might change it to:
Const savePath = _
"C:\Documents and Settings\All Users\Documents\ExcelHelpGiven\_TestFolder\"
Const basicName = "Monthly Expenses"
Static IAmBusy As Boolean
Dim currentDir As String
Dim newName As String
Dim fromCellH2 As Variant

If IAmBusy Then
Exit Sub
End If
IAmBusy = True ' prevent reentry
Application.DisplayAlerts = False
currentDir = CurDir ' so we can restore it later
ChDir savePath ' get ready for the save in proper place

'get whatever is in H2 and put it into
'text format for adding to the filename
If IsEmpty(ThisWorkbook.Worksheets("expense").Range(" H2")) Then
fromCellH2 = " "
Else
fromCellH2 = " " & _
Trim(CStr(ThisWorkbook.Worksheets("expense").Range ("H2"))) & _
" "
End If

newName = basicName & fromCellH2 & _
Format(Now(), "dd-mmm-yyyy") & ".xls"
On Error GoTo CleanupAfterAbort ' just in case!
If Dir$(savePath & newName) < "" Then
'file exists, ask about overwriting
If MsgBox("File:" & vbCrLf & _
savePath & newName & vbCrLf & _
"Already Exists --- Overwrite it?", _
vbYesNo + vbCritical, "Overwrite Old File?") = vbYes Then
ThisWorkbook.SaveAs newName
MsgBox "File has been saved as: " & ThisWorkbook.Name
Else
MsgBox "File Save Cancelled by User"
Cancel = True
GoTo CleanupAfterAbort
End If
Else
'new file, just save it
ThisWorkbook.SaveAs newName
MsgBox "File has been saved as: " & ThisWorkbook.Name
End If
CleanupAfterAbort:
If Err < 0 Then
MsgBox "Error: " & Err.Number & vbCrLf & _
Err.Description & vbCrLf & _
"Took place while trying to save the file!"
Err.Clear
End If
On Error GoTo 0 'reset error trapping
'restore the original default path
ChDir currentDir
Application.DisplayAlerts = True
'reset busy flag
IAmBusy = False
End Sub


"Damil4real" wrote:

How can I get macro to save a workbook to a specific path? And then I
want it to reference a cell in the workbook as part of its file name
when it's saved.

Example:

Save workbook as "Monthly Expenses 1-Sept-08"

The "Monthly Expenses" word is constant, but the date will change
daily.
I want this workbook to be save to in a specific folder in C:\....)
Also a pop-up window should come up asking if user want to overwrite
an already saved file.
If they say yes, then file should be overwritten, and a pop-up window
saying "file saved!" If they say no, then file should not be saved at
all, and a pop-up window saying "file not saved!

Can you please help me ASAP????

THANKS A BUNCH!