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

L

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

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

SiC

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

-Simon
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top