Save as macro

  • Thread starter Thread starter Logan
  • Start date Start date
L

Logan

I would like to create a macro that would take the
information from two different cells and use them as the
file name, then save the file as an .xls in another folder.

The cells I would like to use are named, Surname and
filenumber.

I am then going to create a command button on the toolbar
so that all the employees have to do is click the 'Save As'
button.

I have no problem with creating the shortcut bottons and
assigning the macro to it. It just that I am very new to
VBA and not sure how to write the code, for the save as part.

Any help would be appreciated
 
-----Original Message-----
I would like to create a macro that would take the
information from two different cells and use them as the
file name, then save the file as an .xls in another folder.

The cells I would like to use are named, Surname and
filenumber.

I am then going to create a command button on the toolbar
so that all the employees have to do is click the 'Save As'
button.

I have no problem with creating the shortcut bottons and
assigning the macro to it. It just that I am very new to
VBA and not sure how to write the code, for the save as part.

Any help would be appreciated
.
Click to expand...
Hi,

Try something like this.

Sub Test
Dim sFileName as string
'to get the cell values
sFileName = Range("A1").Value & Range("A2").Value
ActiveWorkbook.SaveAs = sFileName & ".xls"
End Sub

Hope that helps
 
Here is what I've been able to workout so far but can't
find out where to insert the change directory command
I have set up a folder called 'New Files' on my 'C' drive,
this is were I would like to save them at.



Private Sub CommandButton1_Click()
Dim FName1, FName2, Fullname
FName1 = "CK0"
FName2 = Range("AU2").Value & " "
FName3 = Range("J4").Value
Fullname = FName1 & FName2 & FName3
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Fullname, FileFormat _
:=xlNormal, CreateBackup:=False
MsgBox "Saved to " & CurDir
End Sub
 
Sorry about that, Annette.
Here it is... It goes in before 'ActiveWorkbook.SaveAs'


Sub Save_As()
Dim FName1, FName2, FName3, Fullname
FName1 = "CK0"
FName2 = Range("AU2").Value & "-"
FName3 = Range("J4").Value
Fullname = FName1 & FName2 & FName3
Application.DisplayAlerts = False

ChDir "C:\New File"

ActiveWorkbook.SaveAs Fullname, FileFormat _
:=xlNormal, CreateBackup:=False
MsgBox "Saved to " & CurDir
End Sub
 
Back
Top