Save spreadsheet using cell values as filename
 

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

Save spreadsheet using cell values as filename
 

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" wrote in message
...
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