How to click "SAVE" to save a new file with file name derived from a cell?

First of all, please put your code within tags. It makes it easier to be copied for testing.

You need to have a button called Save on your page and have the macro assigned to it. On 2nd thoughts, are you talking about running the macro when you are "saving" the macro file ?

Can I use the "SAVE" button/icon from Excel? Yes, want to run the macro to save a new file once click "SAVE".

I have found the below, but could not get it to work with my SaveAs macro (above).

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
SaveAs
End Sub

You need the sub to be in the Workbook module.

<Alt +F11> to open the VBE. Double click 'ThisWorkbook' in the Project explorer window (top left window). You will then see General at the top left of the Code window and Declarations at the top right of the Code window.

Clicking on General drops down 'Workbook' and creates the default Open sub. Select Beforesave from the declarations dropdown and you'll see this:

Please Login or Register  to view this content.

Thank you very much. I have followed your instruction, but I think I'm not doing it right. After saving saving the code from the VBE, a new Excel file was generated with file name derived from cell A1. However, Excel would crash. Please help.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim FName As String
Dim FPath As String

FPath = "C:\Documents and Settings\Test Folder"
FName = Sheets("Sheet1").Range("A1").Text
ThisWorkbook.SaveAs Filename:=FPath & "\" & FName

End Sub

In the code that you have given above, you have to add:

Please Login or Register  to view this content.

Then go back to your previous workbook

I have added the Workbooks.Add command. When saving, it generates a new excel file with file name derived from cell A1 (great). But this new file does not have any contents from the original file. What did I do wrong? Can you help to add the Workbooks.Add command into my code? Thanks

Originally Posted by arlu1201

In the code that you have given above, you have to add:

Please Login or Register  to view this content.

Then go back to your previous workbook

You have not included the code to copy the contents from the original file to the new workbook. Include that code before the saveas and you should be good to go.

I'm almost there. With the code below, once click SAVE, a new file was generated with file name derived from cell A1. The only bug I'm having now is along with the new (correct) file another file named Book1 also came up. How to get rid of this Book1 file?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim FName As String
Dim FPath As String

FPath = "C:\Documents and Settings\Test Folder"
FName = Sheets("Sheet1").Range("A1").Text

Workbooks.Add
ThisWorkbook.SaveAs Filename:=FPath & "\" & FName

End Sub

Private Sub Workbook_Open()

End Sub

Try one thing. Close all workbooks (save the macro file before closing). Then open the macro file and run again and see if the Book1 opens again. It could be opened in your previous runs of the macro.

Please show me how... thanks

Originally Posted by arlu1201

You have not included the code to copy the contents from the original file to the new workbook. Include that code before the saveas and you should be good to go.

Book1 still came up :-(

Originally Posted by arlu1201

Try one thing. Close all workbooks (save the macro file before closing). Then open the macro file and run again and see if the Book1 opens again. It could be opened in your previous runs of the macro.

Attach your workbook and let me know the fields you want copied and i can help you out.

I think your file got removed. What kind of file did you attach?

I just reattached it. It's an xls file. Thanks

Originally Posted by arlu1201

I think your file got removed. What kind of file did you attach?

Its removed again. I think there is some problem with the file. I get routed to the vbulletin message page to send a note to the administrator.

File name is Test ABC.xls. It's still good. I could download it by double click on the file from my post.

Originally Posted by arlu1201

Its removed again. I think there is some problem with the file. I get routed to the vbulletin message page to send a note to the administrator.

Here is the file

Try this :

Please Login or Register  to view this content.