Thread: Auto Saving a file in a macro to a shared drive

Hi everyone,

I am running a macro in a template file and want to save it to a specific directory with a filename located in cell b20. Below is what I have now. It does a copy and paste values then tries to save to a shared drive. The first time I set it up it saved just fine. But now every time it saves to "My Documents" on my hard drive. I am new to visual basic and have no idea what I need to do. Any help is greatly appreciated. Also, what is the X drive on my computer may be a Z drive on another. How do I make the shared drive path relative?

Thanks in advance

Code:

Sub Auto_Open()
'
' Auto_Open Macro
'
Range("A1:F48").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("JIF_Template_VCCE.xls").Activate
Range("b20").Select
ChDir "X:\F2\VCCE\JIFs\"
fname = Application.ActiveCell
ActiveWorkbook.SaveAs Filename:=fname
End Sub

Trying passing the entire UNC path not just the letter that it's been mapped to (i.e. X on your computer) to the ChDir varaible.