Save Workbook with name generated from excel data

[andymalan]

Hello brainiacks, (said with utmost respect). Please help with my problem:

I have some code in a workbook named configureData.xlsm that creates a filename in Q2 .
I want to open a new workbook.xlsx and save it with the fileName created.

The path would always be the same:

UsersName = Environ("USERNAME")
strFileName = "C:\Users" & UsersName & _
"\Desktop\Excalibur Winner\configureData.xlsm\NewFileName.xlsx"

but the NewFileName will change depending on the data used by the code when creating the filename and storing it in Q2.
Your help will be appreciated hugely.

kind regards
Andy

 

[Logit]

.
"but the NewFileName will change depending on the data used by the code when creating the filename and storing it in Q2."

Where is the data to be selected for the new file name ?

 

[andymalan]

in a workbook named configureData.xlsm

 

[Logit]

.
Based on your answer are we to understand you want the saved workbook to be named " configureData.xlsm " ??

 

[andymalan]

no, as i stated in my request
"but the NewFileName will change depending on the data used by the code when creating the filename and storing it in Q2.
Your help will be appreciated hugely."

in the workbook configureData.xlsm, the existing macro uses the current customers name and quote number and concatenates these into Q1. then the macro copies Q1 and pastes to Q2 using save special values.
i want to use the results of Q2 as the filename. in the path as stated previously.

many thanks
Andy

 

[Logit]

.
Try this :

[COLOR=#333333]"\Desktop\Excalibur Winner\configureData.xlsm\[/COLOR][COLOR=#0000cd]" & Sheets("Sheet1").Range("Q2").Value & ".xlsx[/COLOR][COLOR=#333333]"[/COLOR]

You will need to edit the sheet name to match that sheet you are referencing in your workbook.

 

[andymalan]

"\Desktop\Excalibur Winner\configureData.xlsm\" & Sheets("Sheet1").Range("Q2").Value & ".xlsx"

Dear Logit,
I get a compile error/syntax error when I add your code.

"\Desktop\Excalibur Winner\ExcaliburProPlus\BarCodes.xlsm\" & Sheets("Sheet2").Range("BA1").Value & ".xlsx"

 

[andymalan]

Dear Logit
thank you for your efforts so far, but I am still not able to save the worksheet to the path/filename, the code below no longer gives an error but its still not saving.
Do you have any other advice?

'save workbook AS filename from BA1
    Dim sFile As String
    sFile = Range("BA1").Value & ".xlsm"
    UsersName = Environ("USERNAME")
    strFileName = "C:\Users\" & UsersName & _
    "\Desktop\Excalibur Winner\ExcaliburProPlus\BarCodes.xlsm\" & Sheets("Components").Range("BA1").Value & ".xlsx"
    [\code]
very much in appreciation.

 

[Fluff]

Can you please post the rest of your code, as there is nothing in what you have posted that saves the file.

 

[Logit]

Sub saveShtAs()
Dim MyFolder As String
Dim fname As String, ext As String
Dim TempFolder As String
 
    MyFolder = Environ$("USERPROFILE") & "\Desktop\Excalibur Winner\ExcaliburProPlus"

    If MyFolder = "" Then Exit Sub

    fname = Sheets("Sheet1").Range("Q2").Value

    Sheets("Sheet1").Copy

    ActiveWorkbook.SaveAs Filename:=MyFolder & "\" & fname, _
         CreateBackup:=False
    ActiveWorkbook.Close False
    
MsgBox "Done!"

End Sub