generating file name to use for new workbook save

J

JCE

What code will save a previously unsaved workbook using a file name
generated by data in the new workbook? For example, if cell A1 in the new
workbook has the number 0036 in it, how would you save the new workbook as

0036 myFileName.xls

using the same path as the workbook that this code is running from?

Thx.
 
J

JCE

I might add that the subroutine creates the new workbook and puts the data
in cell A1. I'd like to save the new workbook as a last step in the
subroutine before it ends.
 
J

JCE

This is what works on my windows machine assumming a whole number is in A1
of the original workbook.

Sub Macro1()
Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
newFileNamePath = ThisWorkbook.Path
MsgBox newFileNamePath
newFileName = newFileNamePath & "\" & Range("A1").Value & "
myFileName.xls"
MsgBox newFileName
ActiveWorkbook.SaveAs Filename:= newFileName, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub
 
J

JE McGimpsey

One way:

Dim sFileName As String
With ActiveWorkbook
sFileName = ThisWorkbook.Path & Application.PathSeparator & _
.Sheets(1).Range("A1").Text & " myFileName.xls"
.SaveAs sFileName
End With
 

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