I am trying to use a command button to save a spreadsheet using cell values 
as the file name from the tab Master. For example I want to use cells B2 
(Tony.Dungate) and F2 (2010.March) to create a file name of 
Tony.Dungate.2010.March.xls. Essentialy I would need something like :

Master!B2&.&Master!G2&".xls"

Can someone advise me on the correct code to do this. I have been trying to 
use the following without success:


    Const sRoot     As String = "V:\Database Logs\"

    With ActiveWorkbook
        .SaveAs Filename:=sRoot & _
        .Worksheets("Master").Range("B2").Text, _
                FileFormat:=xlExcel8
        .Close

In your spreadsheet can you enter in a cell say A1 or appropriate.

=B2& "  "&F2  (this will result in "Tony Dungate 2010.March")

Then create the following sub.


Sub filename()

    Dim Name As String
    Range("A1").Select
    Name = ActiveCell
    ActiveWorkbook.SaveAs filename:="V:\Database Logs\" & Name & ".xls", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

End Sub


You may well get some better constructed answers than this.











"Tony" <Tony@discussions.microsoft.com> wrote in message 
news:6FF0F0E0-BA2C-463F-B003-AA7AFCEC747E@microsoft.com...
>I am trying to use a command button to save a spreadsheet using cell values
> as the file name from the tab Master. For example I want to use cells B2
> (Tony.Dungate) and F2 (2010.March) to create a file name of
> Tony.Dungate.2010.March.xls. Essentialy I would need something like :
>
> Master!B2&.&Master!G2&".xls"
>
> Can someone advise me on the correct code to do this. I have been trying 
> to
> use the following without success:
>
>
>    Const sRoot     As String = "V:\Database Logs\"
>
>    With ActiveWorkbook
>        .SaveAs Filename:=sRoot & _
>        .Worksheets("Master").Range("B2").Text, _
>                FileFormat:=xlExcel8
>        .Close