How to save file in a macro taking new name from cell contents?

[LowIQ]

I want to Save-as a file from within a macro, taking the new name from the
worksheet's cell contents. I want to save an order form with a file name
based on the order number contained in a worksheet cell. My question is how
to get the cell contents into the Save-as dialog box when the macro calls the
Save-as function.
Thanks.

 

[Norman Jones]

Hi LowIQ,

Try:

'=============>>
Public Sub Tester004()
Dim sStr As String

sStr = ThisWorkbook.Sheets("Sheet1"). _
Range("A1").Value '<<=== CHANGE

ThisWorkbook.SaveAs Filename:=sStr & ".xls", _
FileFormat:=xlWorkbookNormal

End Sub
'<<=============

 

[SiC]

Assuming your order number is in cell A1,
ActiveWorkbook.SaveAs Filename:=cells(1, 1).value & ".xls"

-Simon

 

[DCSwearingen]

I have used the following technique quite a bit.

the ActiveWorkbook.FullName returns the active workbook name and path;
e.g. C:\My Documents\MyName\MyBook.xls

Private Sub SaveInvoiceNo ()
dim myInvoiceName as string, myNumber as String
myNumber = Worksheets("Invoice").Range("E1").Value
myInvoinceName = ActiveWorkbook.FullName
myInvoiceName = Left(myInvoiceName, Len(MyInvoiceName) - 4)
myInvoiceName = myInvoiceName & "_" & mynumber & ".xls"
ActiveWorkbook.SaveCopyAs Filename:=myInvoiceName
end sub