Thread: automate saving a worksheet with file name found in cell

You could put that line in a Sub in a Module and then assign it to a Form button on the sheet. Or you could use the workbook BeforeSave event. That way any time the users clicks the save icon, it would automatically save the file with the correct filename. This would lead to issues where, if a file with that name already exists, it would ask the user if they want to override the existing file. This can be prevented with

Application.DisplayAlerts = False

Here is the code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.EnableEvents = False ' We turn off event because otherwise we would get caught in a loop of save events.
    Application.DisplayAlerts = False ' This prevents the dialog box opening and asking if the user wants to overwrite an existing file.
    ThisWorkbook.SaveAs Filename:=Sheets("Sheet1").Range("A1") ' Save the file with the appropriate name.
    Application.DisplayAlerts = True ' Turn the alerts back on.
    Application.EnableEvents = True ' Turn events back on
    Cancel = True ' We cancel the save event because we already saved in the lines above.
End Sub

Please see the attached workbook as an example.

To view the code, right-click on any sheet and select "View Code". This will take you to the VBA editor. On the left you should see a navigation pane listing all open workbooks. If your workbook is not already expanded (it should be) then expand it using the + symbol to the left of the workbook name. The double-click on the "ThisWorkbook" module to display the code.

For more information on the VBA editor and modules, do a Google search for VBA basics.

Essentially, each sheet gets its own module. The workbook as a whole gets its own module as well. Each of these modules have pre-set "events" than can trigger custom code. Other events at the workbook level include the Open event, Close, BeforePrint, NewSheet, SheetActivate, etc. Each of these events are tirggered when the user performs an action, such as clicking the "Save" button, or navigating between sheets, or closing the workbook.

Try this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.EnableEvents = False ' We turn off event because otherwise we would get caught in a loop of save events.
    Application.DisplayAlerts = False ' This prevents the dialog box opening and asking if the user wants to overwrite an existing file.
    ThisWorkbook.SaveAs Filename:=Sheets("Sheet1").Range("A1"), FileFormat:=52 ' Save the file with the appropriate name.
    Application.DisplayAlerts = True ' Turn the alerts back on.
    Application.EnableEvents = True ' Turn events back on
    Cancel = True ' We cancel the save event because we already saved in the lines above.
End Sub

For a explaination on the FileFormat number and what it means, see this link: http://www.rondebruin.nl/saveas.htm