Auto generate workbook file name based on multiple cell contents

[naquin316]

Hi all! I'm trying to come up with a macro to save my workbook a certain way. I have a sales invoice that has 6 worksheets and I want to create a picture on the 1st sheet and assign this macro to it. I want to save it to a folder on the desktop called tickets.

I also would like it where no prompts come up, ie. click the button and it saves the file to a new name. I would like the naming convention to look like this:


ticket#_company_wellname.xls Where ticket# is cell A1, company is cell B1, and wellname is cell C1. It is important to have the underscores between names.


any help would be much appreciated.

Ryan

 

[mercatog]

Hi and welcome


Try this code

[pre]

Sub SaveFile()
Dim NameFile As String

With Worksheets("Sheet1")
NameFile = .Range("A1") & "_" & .Range("B1") & "_" & .Range("C1") & ".xls"
End With

ThisWorkbook.SaveAs Environ("USERPROFILE") & "Desktoptickets" & NameFile
End Sub

[/pre]
PS be careful of charcters not admitted to name files such as !?*

 

[naquin316]

Thanks you!! That worked perfectly. Instead of saving it to a certain folder can it be coded where the saveas dialog box comes up with the dim namefile string already there then allowing the user to select his own save location? Thanks again. I learned alot from your first code!

 

[mercatog]

Hi

Try this again

[pre]

Sub SaveFile()
Dim NameFile As Variant

With Worksheets("Sheet1")
NameFile = .Range("A1") & "_" & .Range("B1") & "_" & .Range("C1") & ".xls"
End With

NameFile = Application.GetSaveAsFilename(InitialFileName:=Environ("USERPROFILE") & "Desktoptickets" & NameFile, Filefilter:="Fichier Excel (*.xls), *.xls")

If NameFile = False Then
MsgBox "File not saved"
Else
ThisWorkbook.SaveAs Filename:=NameFile
End If
End Sub

[/pre]

Regards!

 

[naquin316]

I got it to make the save as dialog come up with the correct name. I am also able to select a different folder and click save. Then I get a runtime error '13'.

When I click debug the yellow error points to the line:

If Namefile = False Then

I'm I doing something wrong? I'll try to attach my current code below.

[pre]

Sub SaveFile()
Dim NameFile As String
With Worksheets("SO1")
NameFile = .Range("A1") & "_" & .Range("B1") & "_" & .Range("C1") & ".xls"
End With
NameFile = Application.GetSaveAsFilename(InitialFileName:=Environ("USERPROFILE") & "Desktoptickets" & NameFile, Filefilter:="Fichier Excel (*.xls), *.xls")
If NameFile = False Then
MsgBox "File not saved"
Else
ThisWorkbook.SaveAs Filename:=NameFile
End If
End Sub

[/pre]

 

[mercatog]

Look In the 2nd code

Dim NameFile As Variant

Not As String

 

[naquin316]

Awesome! Works like a champ! Thanks you sooo much for your help.

 

[mercatog]

Glad helping you and thanks for your feedback and kind words

 

[Batman1]

Hi mercatog


How can I get the first part of your cod to save the file as an .xlsm it works fine as .xls but I need the user to run macros in the work sheet after they have saved it.


Hope you can assist