Saving a spreadsheet in specific directory

[Revelation]

I would like to create a button that will save the spreadsheet as a different name in the format "Surname-Month.xls". Surname has the value from cell B10 and is in sheet 'Estimate'. The month will be able to calculated from the computers clock and in the format of 3 letters long. The spreadsheet will be saved in a directory named the year (2004) that directory will be in the directory the current spreadsheet is held. I hpe you understand me.
Eg. system.xls is currently saved in C:\spreadsheet\
cell B10 in sheet 'Estimate' has the value 'smith'
The month is March.
The save button is clicked:
C:\spreadsheet\2004\smith-Mar.xls is created.
Hope this is possible thanks in advance Adam.

 

[santeria]

Saving as a file with Name, variable date, is easy enough.... it's the directory creation that gives me a headache.

 

[onlyadrafter]

Hello

Is this OK?

Sub SAVE_AS()
ActiveWorkbook.SaveAs ("c:\spreadsheet\2004\" & Sheets("Estimate").Range("B10").Value & "-" & Format(Month(Now), "mmm"))
End Sub

This assumes c:\spreadsheet\2004 directory exists.

 

[DRJ]

Try This

Sub SaveIt()

Dim MyPath as String
Dim MyName as String

    MyPath = thisworkbook.Path & "\2004"
    MyName = Sheets("Estimate").Range("B10").Value & " " & Format(Date, "mmm") & ".xls"

    ActiveWorkbook.SaveAs Filename:= MyPath & "\" & MyName

End Sub

 

[Revelation]

Part of it is yes. Its just the directory part. I was hoping you could have more flexibility and not specify the directory, it finds it from where the system is saved.
edit: DRJ that might be what I'm after tyvm ill try it now. :D
is there any way you can change this to a variable that changes over time? "/2004" thx.

 

[DRJ]

Your Welcome. If you run into any problems let me know.

Take Care

 

[Revelation]

Ok, i edited it abit to make a variable year directory (2004, 2005 etc.) and changed the save format to: Surname-day-month.xls but i get run time error 1004. what have I messed up?

Dim MyPath As String
Dim MyName As String

    MyPath = ThisWorkbook.Path & "\" & Format(Date, "yyyy")
    MyName = Sheets("Estimate").Range("B10").Value & "-" & Format(Date, "dd") & "-" & Format(Date, "mmm") & ".xls"

    ActiveWorkbook.SaveAs FileName:=MyPath & "\" & MyName

 

[Revelation]

nah my fault :x
/me slaps
thanks for the help guys, much appreciated :D