This might do it:

[VBA]

Sub MyCopySheet()
Dim sPath$, sName$

'Change this, don't forget the trailing \
sPath = "e:\test\"

'Cell with the name is A1 on active sheet, change this
sName = ActiveSheet.Range("A1").Value

ActiveSheet.Copy
ActiveSheet.SaveAs sPath & sName
End Sub

[/VBA]