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
'<<=============


---
Regards,
Norman



"LowIQ" <[email protected]> wrote in message
news:[email protected]...
>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.

[SiC]

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

-Simon

"LowIQ" wrote:

> 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.

[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