"saveas" macro with Cell Value as filename

[keith]

Anyone ever automated the saveas process utilizing a cell value for PART of the filename?
I need to put a button on the sheet that saves the file with the cuurent filename + contents of a cell (k2).. for example if the current filename is "file1" and the contents of Cell K2 is "test" I need a macro to save the workbook as "file1_test.xls"

Here is what I have but don't know how to integrate the contents of a cell..

ChDir "I INSERT PATH HERE"
ActiveWorkbook.saveas Filename:= _
"I INSERT FULL PATH & FILENAME HERE", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

[Tom Schreiner]

Hi

Here is what I have but don't know how to integrate the contents of a cell..

ChDir "I INSERT PATH HERE"
ActiveWorkbook.saveas Filename:= _
"I INSERT FULL PATH & FILENAME HERE", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

assuming that K2 is on sheet1

ChDir "C:SaveMeNow"
ActiveWorkbook.saveas Filename:= _
"C:SaveMeNowfile1_" & sheet1.range("K2").value & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False


Do not use the double , just single forward slashes

Tom



[ This Message was edited by: TsTom on 2002-03-26 11:53 ]

[keith]

Ok - Got it - I had to use
Worksheets("Sheet1").Range("K2")
for the sheet/cell reference cause
ssworksheet.Range(C6).Value
kept giving me runtime errors..

but the &'s worked great..

Thanks

[keith]

sorry cut and pasted wrong line..

Sheet1.Range(K2).Value

was giving me runtime errors..

[bingo525i]

HI,
I have the same problem, I want to modify the file name field in the Save As dialog to put in it the value of the cell G3.

I don't know how to do that and in what event should I add a code.....I tried to put a code in the Workbook_BeforeSave event, yet I am getting two consecutive save as dialogs....
ANy help would be much appreciated......

[mooseman]

This can be added to the button code in the On_click. just change folder (path) and sheet number as needed

Sub Saveasfilename()
Dim fname, folder, cvalue, NewFname As String

' Macro by mooseman
'
fname = ActiveWorkbook.Name           'Gets the active workbook's current name
folder = "C:\test\"                   'put in the path to where you want the file saved
cvalue = Sheets(1).Range("K2").value  ' assumes that the K2 cell is in the first sheet of the workbook
NewFname = folder & fname & "_" & cvalue & ".xls"  'puts it all together

'Saves the currect workbook with the new name
    ActiveWorkbook.SaveAs Filename:=NewFname, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    
End Sub

[bingo525i]

Thanks,
I think using a button is a good idea...