Save As - using data from two cells

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

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
 
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
 
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
 
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.
 
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
 
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
 
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
 
Thats working,
Thanks Joseph

Now all have to do is work on the directory issue, but
that's another lesson.

Thanks again for the lesson
Tim
 
No problem Tim, glad its sorted. About this directory issue, you want t
change where its saved to? Ok, in the VBE select the ThisWorkboo
object and then go to the Open event.

Stick this code in there:

With Application
.UserName = "John Doe"
.StandardFont = "Your font selection"
.StandardFontSize = "Your font size selection, in points"
.DefaultFilePath = "Where you want it saved to each time"
.AltStartupPath = "Your alternate startup path"
.EnableSound = False
.RollZoom = False
End With

This allows you to specify where you want the workbook saved each tim
by setting the Default File Path as soon as the user opens th
document.

Cheers
 
Back
Top