I have a workbook template with multiple sheets that is
saved as a .xlt.
I would like to use the data from two cells (AU2 & J4) to
used as the .xls file name when the file is saved.

Right everytime I go to Save As the default is still
automatically set as Template.

Is there any way to force it to use the data from the two
cells and revert to .xls everytime new data is enter in the
template.

Sub Commandbutton1_click()
'Ok, what we do is store the cell values to the first two variables
'like so:
Dim FName1 as string, FName2 as string, Fullname as string

'Then we tell Fname1 and 2 what to be by referencing your cells to
them:

FName1 = Range("AU2").Value
FName2 = Range("J4").Value
Fullname = FName1 & FName2

'Then we tell it to save using Fullname as the filename:

Activeworkbook.Saveas Fullname, FileFormat:=xlNormal,
Createbackup:=False

End Sub
'You could also control where the workbook is saved using the following
code:

ChDir "Your path here"

Hope that helps
Cheers


---
Message posted from http://www.ExcelForum.com/

Joseph, this is what I did

Sub Commandbutton1_click()
Dim FName1 As String, FName2 As String, Fullname As String
FName1 = Range("AU2").Value
FName2 = Range("J4").Value
Fullname = FName1 & FName2
Activeworkbook.Saveas Fullname, FileFormat:=xlNormal,
Createbackup:=False

End Sub

The line:
Activeworkbook.Saveas Fullname, FileFormat:=xlNormal,
Createbackup:=False
is highlighted in red and when I go to Save As nothing changed.

If you can't tell, I new to the VBA part of this...

Thanks
Tim



-----Original Message-----
Sub Commandbutton1_click()
'Ok, what we do is store the cell values to the first two
variables
'like so:
Dim FName1 as string, FName2 as string, Fullname as string

'Then we tell Fname1 and 2 what to be by referencing your
cells to
them:

FName1 = Range("AU2").Value
FName2 = Range("J4").Value
Fullname = FName1 & FName2

'Then we tell it to save using Fullname as the filename:

Activeworkbook.Saveas Fullname, FileFormat:=xlNormal,
Createbackup:=False

End Sub
'You could also control where the workbook is saved using
the following
code:

ChDir "Your path here"

Hope that helps
Cheers


---
Message posted from http://www.ExcelForum.com/

.

Sorry Tim, my mistake, there was a syntax error in that lineof code,
this is how it should look:

ActiveWorkbook.SaveAs Fullname, FileFormat_:=xlNormal,
CreateBackup:=False

'Createbackup:=false' should be on the same line as
Activeworkbook.blahblahblah. You can put this code into a button by
creating it from yourcontrol toolbox (don't want to be patronizing, but
I don't know how new you mean when you say you're new). Then just
double click the button and stick your code in there. Alternatively if
you want to do this when 'save as' is clicked you can stick the code
into the BeforeSave event of the Activeworkbook. So your code should
look like this:

Dim FName1 As String, FName2 As String, Fullname As String
FName1 = Range("AU2").Value
FName2 = Range("J4").Value
Fullname = FName1 & FName2
ActiveWorkbook.SaveAs Fullname, FileFormat_:=xlNormal,
CreateBackup:=False


If that still doesn't work, I'll post a workbook up here with the code
in it.

Cheers


---
Message posted from http://www.ExcelForum.com/

Very new

I think I'm getting closer. I created the control button
and pasted the code in as;
Private Sub CommandButton1_Click()
Dim FName1, FName2, Fullname
FName1 = Range("AU1").Value
If FName1 = "" Then End
FName2 = Range("B1").Value
If FName2 = "" Then End
Fullname = FName1 & FName2
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Fullname, FileFormat _
:=xlNormal, CreateBackup:=False
End Sub

But I get error message Compile error named argument not
found "Private Sub...." is highlighted in yellow and
FileFormat_:= is highlight in blue.

I appreciate the help
Tim
and

-----Original Message-----
Sorry Tim, my mistake, there was a syntax error in that
lineof code,
this is how it should look:

ActiveWorkbook.SaveAs Fullname, FileFormat_:=xlNormal,
CreateBackup:=False

'Createbackup:=false' should be on the same line as
Activeworkbook.blahblahblah. You can put this code into a
button by
creating it from yourcontrol toolbox (don't want to be
patronizing, but
I don't know how new you mean when you say you're new).
Then just
double click the button and stick your code in there.
Alternatively if
you want to do this when 'save as' is clicked you can
stick the code
into the BeforeSave event of the Activeworkbook. So your
code should
look like this:

Dim FName1 As String, FName2 As String, Fullname As String
FName1 = Range("AU2").Value
FName2 = Range("J4").Value
Fullname = FName1 & FName2
ActiveWorkbook.SaveAs Fullname, FileFormat_:=xlNormal,
CreateBackup:=False


If that still doesn't work, I'll post a workbook up here
with the code
in it.

Cheers


---
Message posted from http://www.ExcelForum.com/

.

Tim,

No problem, thats what this place is for, its also a learning curve fo
me too! Now, I've posted my workbook up here so that you can view th
code as I've set it up. Its probably just the way the code is set ou
on here, sometimes line wrapping can be a headache

Attachment filename: one two.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=42756
--
Message posted from http://www.ExcelForum.com

I found out why the red highlight, "the
Createbackup:=False",line was dropped down a line.
but still can't seem to get it to Save As with the data
from those cells. Still uses the original Workbook name and
as a Template.


-----Original Message-----
Joseph, this is what I did

Sub Commandbutton1_click()
Dim FName1 As String, FName2 As String, Fullname As String
FName1 = Range("AU2").Value
FName2 = Range("J4").Value
Fullname = FName1 & FName2
Activeworkbook.Saveas Fullname, FileFormat:=xlNormal,
Createbackup:=False

End Sub

The line:
Activeworkbook.Saveas Fullname, FileFormat:=xlNormal,
Createbackup:=False
is highlighted in red and when I go to Save As nothing
changed.

If you can't tell, I new to the VBA part of this...

Thanks
Tim



-----Original Message-----
Sub Commandbutton1_click()
'Ok, what we do is store the cell values to the first two
variables
'like so:
Dim FName1 as string, FName2 as string, Fullname as string

'Then we tell Fname1 and 2 what to be by referencing your
cells to
them:

FName1 = Range("AU2").Value
FName2 = Range("J4").Value
Fullname = FName1 & FName2

'Then we tell it to save using Fullname as the filename:

Activeworkbook.Saveas Fullname, FileFormat:=xlNormal,
Createbackup:=False

End Sub
'You could also control where the workbook is saved using
the following
code:

ChDir "Your path here"

Hope that helps
Cheers


---
Message posted from http://www.ExcelForum.com/

.

.

Here we are Tim, the finished article:

Private Sub CommandButton1_Click()
Dim FName1, FName2, Fullname
FName1 = Range("AU1").Value
If FName1 = "" Then End
FName2 = Range("B1").Value
If FName2 = "" Then End
Fullname = FName1 & FName2
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Fullname, FileFormat _
:=xlNormal, CreateBackup:=False
End Sub

Notice the if statements, they are there to stop Excel trying to save
files with no name defined. There must be values in both cells for the
code to work.

Hope this helps


---
Message posted from http://www.ExcelForum.com/