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]

My pleasure.

Hi Mumps,

Apologies for being a pain.....on the code below, I'm getting an error now with a "Run-time error '13' Type mismatch " and I have tried varying changes to the code. The code breaks on the line starting with "sName = Join". Can you or someone assist?

Sub CreateDECOCollateral ()

Dim Path As String, sName As String
Dim filename As String
Dim wb As Workbook

sName = Join(Application.WorksheetFunction.Transpose(Range("B5", Range("B" & Rows.count).End(xlUp)).Value), " ")

Sheets("DECO Collateral").Copy

Path = "G:\DATA\BACKOFF\SETTLEME\Derivatives\OTC\Fund Launches\DECO Aladdin\DECO - Instruction\"

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

ActiveWorkbook.Close

End Sub

 

[mumps]

Has anything changed since the macro last worked properly?

 

[StevieMP]

Morning Mumps....

Good to hear from you again and thank you in advance.
No, it hasn't changed which is weird!

I have included 2 screenshots showing the input being based off the information starting in B5 through to J6.
Basically when saving, I want to create a copy of the sheet where 'sName' is based from the input in B5 and then the code of in bold :

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

Is it possible to save the sheet using the information in B5 & B6, e.g. 'Test' and using the information in E5 & E6, e.g. 'HSBC', 'UBS'

G:\DATA\BACKOFF\SETTLEME\Derivatives\OTC\Fund Launches\DECO Aladdin\DECO - Instruction\Test - HSBC UBS - DECO Collateral 20-05-2022.xlsx

 

[mumps]

I'm not sure I understood correctly but try:

Sub SaveFile()
    Dim Path As String
    Sheets("Steve").Copy
    Path = "C:\MyFolder\"
    ActiveWorkbook.SaveAs Filename:=Path & Range("B5") & "-" & Range("E5") & " " & Range("E6") & "-" & "DECO Collateral " & Format(Now(), "DD-MM-YYYY") & ".xlsx"
End Sub

 

[StevieMP]

Thank you Mumps, that seems to work - is there a way to make it more dynamic?
So in other words if there is information within column B & E starting at B5 downwards & E6 downwards it is picked up automatically within the saving of the file?

 

[mumps]

is there a way to make it more dynamic?

Do you mean that the file will be saved multiple times with different names? It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[StevieMP]

Hi Mumps,
No it's not to save the file multiple times.
I will try and save the spreadsheet. I can't save the file!!!!

Basically it's to copy that tab and then rename the spreadsheet using the data that's within column B from B5 onwards and E from E5 onwards. So if there is just 'Test' in column B and in column E you have 'HSBC' then the file should save as :

G:\DATA\BACKOFF\SETTLEME\Derivatives\OTC\Fund Launches\DECO Aladdin\DECO - Instruction\Test - HSBC - DECO Collateral 20-05-2022.xlsx

Or, if the spreadsheet has again 'Test' in column B in both B5 & B6 and in column E you have 'HSBC' in E5 and UBS in E6 then the file should save as:

G:\DATA\BACKOFF\SETTLEME\Derivatives\OTC\Fund Launches\DECO Aladdin\DECO - Instruction\Test - HSBC UBS - DECO Collateral 20-05-2022.xlsx

So, if then if 'Test' was also added in B5 & 'ABCD' in B6 and then in E5 'SSB' and E6 'JPMS' when saving the file should save as :

G:\DATA\BACKOFF\SETTLEME\Derivatives\OTC\Fund Launches\DECO Aladdin\DECO - Instruction\Test ABCD - SSB JPMS - DECO Collateral 20-05-2022.xlsx

 

[mumps]

Please see my instructions in Post #16 to attach a screen shot or upload a file which shows the possibilities you described and a few more possibilities so I can see the pattern.

 

[StevieMP]

Hi Mumps,
I have saved the file to my dropbox:

StevesSheet.xlsx

Shared with Dropbox

www.dropbox.com

See link above

So details are:
I want to resave the file via vba.

Scenarios are to save to a path specified (see code):

Dim Path As String, sName As String
Dim filename As String
Dim wb As Workbook

sName = Join(Application.WorksheetFunction.Transpose(Range("B5", Range("B" & Rows.count).End(xlUp)).Value), " ")


Sheets("Steve").Copy

Path = "G:\DATA\BACKOFF\SETTLEME\Derivatives\OTC\Fund Launches\DECO Aladdin\DECO - Instruction\"

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

ActiveWorkbook.Close

End Sub

So, in the spreadsheet I would like to save the file based on the data input in both columns B & E in whatever is written starting at B5 & E5

If there was only one entry of 'Test' in B5 and one entry of say 'HSBC' in E5 then the file when saved should be:

Test - HSBC - DECO Collateral 23-05-2022.xlsx

In the example in the dropbox in column B5 & B6, I have 'Test' and 'Test' and in E5 & E6 you have 'HSBC' and 'UBS' so the file should save as:

Test Test - HSBC UBS - DECO Collateral 23-05-2022.xlsx

If I decided to enter 3 lines say 'Test', 'Test', & 'ABCD' in B5, B6, B7 and add 'HSBC' in E5, 'UBS' in E6 and 'RBC' in E7 then when saved the file would save as:

Test Test ABCD - HSBC UBS RBC - DECO Collateral 23-05-2022.xlsx

Does this help?

 

[mumps]

Try:

Sub SaveFile()
    Dim Path As String, sName1 As String, sName2 As String
    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), " ")
    Sheets("Steve").Copy
    Path = "G:\DATA\BACKOFF\SETTLEME\Derivatives\OTC\Fund Launches\DECO Aladdin\DECO - Instruction\"
    MsgBox Path & sName1 & " - " & sName2 & " - DECO Collateral" & " " & Format(Now(), "DD-MM-YYYY") & ".xlsx"
    ActiveWorkbook.SaveAs Filename:=Path & sName1 & " - " & sName2 & " - DECO Collateral" & " " & Format(Now(), "DD-MM-YYYY") & ".xlsx"
End Sub