Relax, young grasshopper - the ox is slow, but the earth is patient. It
takes time to:
read and comprehend a request, code and test a possible solution, and then
get it all back here for you to use.

I believe the code below will do what you want. It 'intercepts' the normal
workbook save process to do all that you want. You will need to change one
Const value that's set up in it, the path you want it saved into, once you've
copied it into your workbook.

This code works with the Workbook_BeforeSave event, and so it MUST go into a
particular area in the workbook. To get to that area:
Open your workbook.
RIGHT-click on the little Excel icon that is immediately to the left of the
word File in the Excel menu bar. Choose [View Code] from the popup list that
should appear.

Copy the code below and paste it into the code module that was presented to
you when you chose [View Code]. CHANGE the Const savePath to = "your real
path" (but actually put the path in between the quote marks and be sure there
is a path separator (\ for Wintel machines, : for Macs) at the end of that
path). There's an example of a revision to that line of code in the code
below.

Once you've done this properly, each time you try to save the workbook, it
will do as you have asked. The only way to save it somewhere else is going
to be to copy it from your special folder to where ever else you want a copy.

The code:

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

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
newName = basicName & 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!