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.

 

[StevieMP]

Thank you mumps.

It works if I am in the spreadsheet on the actual sheet and have the vba screen open and step into the code, however if I run the code from another sheet in the same workbook, (I have a button in the same workbook on another sheet) the file name changes to sheet name where the button is!

 

[mumps]

Try:

Sub SaveFile()
    Dim Path As String, sName1 As String, sName2 As String
    Path = "G:\DATA\BACKOFF\SETTLEME\Derivatives\OTC\Fund Launches\DECO Aladdin\DECO - Instruction\"
    With Sheets("Steve")
        sName1 = Join(Application.WorksheetFunction.Transpose(.Range("B5", .Range("B" & .Rows.Count).End(xlUp)).Value), " ")
        sName2 = Join(Application.WorksheetFunction.Transpose(.Range("E5", .Range("E" & .Rows.Count).End(xlUp)).Value), " ")
        .Copy
    End With
    ActiveWorkbook.SaveAs Filename:=Path & sName1 & " - " & sName2 & " - DECO Collateral" & " " & Format(Now(), "DD-MM-YYYY") & ".xlsx"
End Sub

 

[StevieMP]

Hi Mumps,

Apologies for the delayed response - I have been away!

Thank you for the above code.
I have updated the code to reflect what you have written, however when I click the macro button in a sheet called "Control" the code is taking what I have in cell B3 (Control - Create Spreadsheets Required) in the "Control" tab and adding that to the :

ActiveWorkbook.SaveAs filename:=Path & sName1 & " " & sName2 & " - DECO Collateral" & " - " & Format(Now(), "DD-MM-YYYY") & ".xlsx"

e.g.
Control - Create Spreadsheets Required - DECO Collateral - 13-06-2022.xlsx

rather than what's in the sName1 and sName2.

 

[mumps]

That is strange because there is no reference to cell B3 in the code. Can you upload a copy of the file which contains the "Control" sheet?

 

[StevieMP]

Hi Mumps,
I cannot seem to load the mini sheet image from clipboard as I cannot locate it using the xl2bb when trying to upload image!
All there is, is another sheet called "Control".
On that sheet there is a macro button called "Create DECO Collateral Spreadsheet" (highlighted in yellow) and your code sits behind it.
When the button is clicked it saves and takes what is written in the "Control" sheet in cell B3.

 

[mumps]

The code refers to the range in in column B starting at B5 and to the range in column E starting in E5. The Control sheet has no data to refer to in columns B and E.

 

[StevieMP]

I know that's what I could not understand!

 

[mumps]

Unless columns B and E have data starting in row 5, the macro will not work.

 

[StevieMP]

Hi Mumps,
I've got it working correctly......school boy error on my behalf, I'm really sorry!
I had missed the dot before the Range and Rows

My code
(Range("B5", Range("B" & Rows.count).End(xlUp)).Value), " ")

Your code
(.Range("B5", .Range("B" & .Rows.count).End(xlUp)).Value), " ")

It all works correctly and saves as expected, so thank you once again.

 

[mumps]

My pleasure.