Generating Save As Name

**zoolander_10** · 11-20-2009, 11:40 AM

I will be using my Excel worksheet for quoting individual customers and I am wondering whether I can specify that when the save button is pressed that it generates the destination file name from a cell or cells. eg the Customers name and registration. rather than using "save as" option.

Thanks again

**Andy Pope** · 11-20-2009, 11:48 AM

This code goes in the Thisworkbook object.

Uses the contents of A1 and B1 on sheet with a code name of Sheet1.
You may want to control the path as well as the filename

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

    Dim strName As String
    
    If SaveAsUI Then
        strName = Sheet1.Range("A1") & "_" & Sheet1.Range("B1").Value
        ThisWorkbook.SaveAs strName
        Cancel = True
        Exit Sub
    End If
    
End Sub

**zoolander_10** · 11-20-2009, 12:07 PM

Brilliant, sorry to sound stupid but where do i enter the code.

**Andy Pope** · 11-20-2009, 12:08 PM

right click a sheet tab and pick View code.
In the project window double click the Thisworkbook object

paste the code in there.

**zoolander_10** · 11-20-2009, 12:49 PM

Hi,

I have attached the workbook. I "think" i have followed your instructions correctly however when i press save I don't get the desired result.

I need the save name to be B3 & B4 eg in this work book Chris Killen LR54UTM.

Any help would be greatly appreciated.

Thank you

**Andy Pope** · 11-20-2009, 12:55 PM

The code is working but it only works when you do a SAVEAS.

If the workbook has already been saved then the name will not be changed.

If you use the menu File > Saveas you will see the file is saved with the new details.