MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Correction: Save completed worksheet in the same folder as original

Posted by Tayo Agboola on June 12, 2001 12:22 PM

I made an excel form which I want people to fill out and it to be named/saved as the value entered into a specific cell in the folder where the original template is located. My problem is that when it saves the newly named worksheet saves to my desktop (frequently), to the right folder (occassionally) and to the folder where I last saved something (a whole lot more than I would like!). I have multiple copies of this form addressed to different vendors and don't want to specify the exact dir and folder in the macro. Your help will be greatly appreciated.

Here is the code as it is now:
Sub Save_it()
' Save_it Macro
' Macro recorded 5/15/2001 by Ty Agboola

Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
SaveFile = Range("d8").VALUE
ActiveWorkbook.SaveAs Filename:=SaveFile, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
End Sub

Thanks in advance.

Posted by Joe Was on June 12, 2001 2:03 PM

This is the code to pull the SaveAs user box.

Sub MySave()
'This saves by displaying the SaveAs user box.
'This is keyed to the Ctrl-s hot key in macro-options.
'By Joe Was, 6-12-2001

Dim MyFileName As String
MyFileName = Application.GetSaveAsFilename
If MyFileName = "False" Then Exit Sub
ActiveWorkbook.SaveAs MyFileName
End Sub

By default it should pull the current directory and drive?

If not you can change it befor the save.

Save_it Macro Macro recorded 5/15/2001 by Ty Agboola