VBA for file save as macro enabled to a specifi path and folders

Hi All
Can you please assist me with writing a vba script that will save the workbook as a macro-enabled workbook to a specific path and folder and sub folders.
The workbook name should default to the same as Final Sheet, Cell B4, followed by space hyphen space and Cell B9 in format yyyymmdd OR can be user defined. I tried using a borrowed code, but it would not let me save as a file name, but came up to the Folder name only.
Can you please help? Thanks

Hi stpeter,

I can supply the VB script (macro) but it would have to be saved in a module in a workbook named "Personal.xls" in Excel 2003 (and I think it's "Personal.xlsb" in Excel 2007) which then has to saved in C:\Program Files\microsoft office\office\XLSTART.
This file will "open" when you start Excel so the macro will be available.
When you have updated your original spreadsheet and you want to save it, run the macro and it will save as an "xlsm).
The next time you run the macro it will ask you if you want to replace the one you saved previously.
Is this what you want ?

Regards

peterrc

yes please...
and thanks

Hi stpeter,

The code is as follows:-

[Sub SaveAs()

ChDir "C:\Excel"
ActiveWorkbook.SaveAs Filename:="C:\Excel\Name of your choice..xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub]

You can create this yourself by clicking "Record a macro" and then save the file in xlsm format.
However the macro is now "linked" to the xlsm so when you open the original xlsx the macro will not be available, hence the need to save it in XLSTART as per my original reply.

If you do use the above, change C:\Excel\Name of your choice..xlsm to where you want to save and what you want to name it (you could also attach the macro to a command button on your original spreadsheet).

Regards

peterrc

Hi Peterrc:
Thank you for your response. I am pasting a sample file.
I tried to put in the formula but it gives me an error.
What I am looking for is that the location is path specific and the name is pulled from the worksheet “Final” and range “B2”). I also want the name to have a dash (-), and the start date “B2” in the format yyyymmdd.

I will be posting another thread relating to the same workbook but a different issue- fyi only.

I have sorted this out. Thank you for your assistance.