Excel 2010 Macro Save AS

[robert222]

Hi<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>Can anyone help, I am looking for a macro that once run will a copy of the workbook with a name based on the contents of cell AL61 on a sheet named MAIN SHEET but allow the user to specify the path.<o></o>
I have very little experience with programing. Can anyone help.<o></o>

 

[DauntingGecko]

Private Sub cmdGetSaveAsName_Click()
Dim save_as As Variant
Dim file_name As String

file_name = Sheets("MAIN SHEET").Range("AL61")

    ' Get the file name.
    save_as = Application.GetSaveAsFilename(file_name, _
        FileFilter:="Excel Files,*.xls,All Files,*.*")
    ' See if the user canceled.
    If save_as = False Then Exit Sub

    ' Save the file with the new name.
    If LCase$(Right$(save_as, 4)) <> ".xls" Then
        file_name = save_as & ".xls"
    End If
    ActiveWorkbook.SaveAs Filename:=save_as
End Sub

 

[robert222]

Thanks you

That seems to work but does not add a file extension when it saves.

Is there a way to have it save with a .xls or .xlsm extension.

Regards

Robert

 

[Andrew Poulsom]

The last line should be:

ActiveWorkbook.SaveAs Filename:=file_name

And you should read this:

http://www.rondebruin.nl/saveas.htm

 

[robert222]

Thanks for your help, that worked a treat.

Will read the article.

 

[ghp2017]

Hello,
When I use this code .xlsm to the actual file name. What do I need to do to stop that from being added?