Using "Save As" with a file name selected by cell

I am trying to save a worksheet where the Save As dialog box appears so the
user can choose the location. I need the file name to be predefined from a
cell entry.

The following works by saving the file as "Data - (A1).xls" where A1 is a
variable but it saves in the current folder only. How can I use the Save As
dialog box so that the new file name appears by default and the user can then
select the location?

Sub SaveWithVariableFromCell()
Application.DisplayAlerts = False
Dim SaveName As String
SaveName = ActiveSheet.Range("A1").Text
ActiveWorkbook.SaveAs Filename:="Data - " & SaveName & ".xls"
End Sub

Many thanks

So you want to allow the user to control the drive/folder, but you want to
specify the filename?

If yes, maybe just asking for the drive/folder would be better:

Jim Rech has a BrowseForFolder routine at:
http://www.oaltd.co.uk/MVP/Default.htm
(look for BrowseForFolder)

John Walkenbach has one at:
http://j-walk.com/ss/excel/tips/tip29.htm

If you and all your users are running xl2002+, take a look at VBA's help for:
application.filedialog(msoFileDialogFolderPicker)

B Baggins wrote:
>
> I am trying to save a worksheet where the Save As dialog box appears so the
> user can choose the location. I need the file name to be predefined from a
> cell entry.
>
> The following works by saving the file as "Data - (A1).xls" where A1 is a
> variable but it saves in the current folder only. How can I use the Save As
> dialog box so that the new file name appears by default and the user can then
> select the location?
>
> Sub SaveWithVariableFromCell()
> Application.DisplayAlerts = False
> Dim SaveName As String
> SaveName = ActiveSheet.Range("A1").Text
> ActiveWorkbook.SaveAs Filename:="Data - " & SaveName & ".xls"
> End Sub
>
> Many thanks

--

Dave Peterson