Saving a File via VBA using several Excel cells

[StevieMP]

Hi There,

I want to save a spreadsheet using VBA and the name of the file I want to use can be what has been input in several cells
e.g.
A1, (example Test)
A2, (example Test1)
A3 (example Test2)
etc...

My code is the following:

Dim Path As String
Dim filename As String

Sheets("Steve").Copy
Path = "C:\MyFolder\"
Filename = Range("A1")
ActiveWorkbook.SaveAs filename:=Path & filename & Format(Now(), "DD-MM-YYYY") & ".xlsx"


Is there a way to be able to save the file using what has been input in A1, A2 & A3 so the file saved becomes:
C:\MyFolder\Test Test1 Test2 11-03-2022.xlsx

Thank you for any help and suggestions.

 

[mumps]

Try:

Dim Path As String
Sheets("Steve").Copy
Path = "C:\MyFolder\"
ActiveWorkbook.SaveAs filename:=Path & Range("A1") & " " & Range("A2") & " " & Range("A3") & " " & Format(Now(), "DD-MM-YYYY") & ".xlsx"

 

[StevieMP]

Thank you mumps - Is there a way to make it dynamic, so if the next time Cell A4 is populated Range("A4") is automatically added ?

 

[mumps]

How many cells do you plan to add?

 

[StevieMP]

Possibly between 1 to 6 cells, maybe at a push 7.
So, if there are only 3 inputs from cell A1 to A3 and nothing in cells A4 to A7 would that matter the latter being empty if all A1 to A7 was listed in the code?

 

[mumps]

Assuming that there is no other data in column A other what will be used in the file name, try:

Sub SaveFile()
    Dim Path As String, sName As String
    sName = Join(Application.WorksheetFunction.Transpose(Range("A1", Range("A" & Rows.Count).End(xlUp)).Value), " ")
    Sheets("Steve").Copy
    Path = "C:\MyFolder\"
    ActiveWorkbook.SaveAs filename:=Path & sName & " " & Format(Now(), "DD-MM-YYYY") & ".xlsx"
End Sub

 

[StevieMP]

mumps - that has worked a treat! I cannot thank you enough.

 

[mumps]

You are very welcome.

 

[StevieMP]

To coin a Frank Spencer quote :
"Every day, in every way, I'm getting better and better."

Really appreciate your help.

 

[mumps]

My pleasure.