Save As file name-Macro

[dlove]

Ok,

I have a macro recorded that saves the workbook to my desktop and then saves an additional copy to a shared drive. Right now it wants to save my file as the same name every time and ask to replace the existing copy.

What I would like to tell it to do is make the save as name reference cell D10. Unfortunatley, I don't know how to tell it to do that in visual basic.

Any assistance?

 

[Legacy 68668]

dlove

ChDir "Z:\Quoting Inventory Base Data"

Will not work, if Drive Z is on the Network.
Get the shared name for that drive that begins from "\\"

Sub SaveQuote()
Dim myDeskTop As String
myDeskTop = CreateObject("WScript.Shell").SpecialFolders("desktop")
ActiveWorkbook.SaveAs Filename:= _
myDeskTop & "\" & Sheets("Sheet1").Range("D10").Value & ".xls", _
FileFormat:=xlExcel8, Password:="VelvetSweatshop", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
CreateObject("WScript.Shell").CurrentDirectory = _
      " \\server\Quoting Inventory Base Data\ "  '<- change here
ActiveWorkbook.SaveAs Filename:= _
Sheets("Sheet1").Range("D10").Value & ".xls", FileFormat:= _
xlExcel8, Password:="VelvetSweatshop", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Range("Q36").Select
End Sub

<!-- / message -->