Need a way of creating a reference number

[dpaton05]

I have created spreadsheet that creates quotes successfully but my supervisor has asked me to add some sort of a reference so the quote can be identified.

• There is a file client_list.xlsm that has the client name added too when the quote is finalised
• This code will be run from my quoting spreadsheet
• I also want a reference number to identify quotes
• When the quote is finalised, I want the reference number stored at the end of column A, in sheet "Reference", in the file client_list.xlsm to have a 1 added to it, and copied to the cell below it. I then want the new number to be put into H5 of my quoting spreadsheet, of the sheet NPSS_quote_sheet.

I have code to add the name and I tried to edit it and add a bit to add the reference number but it wouldn't work. Could someone help me with the vba code please?

Sub AddName()

Dim wb1 As Workbook, wb2 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
Dim f As Range, client As Variant, reference As Integer
    
    Workbooks.Open fileName:=ThisWorkbook.Path & "\client_list.xlsm"
    
    Set wb1 = ThisWorkbook
    Set sh1 = wb1.Sheets("npss_quote_sheet")
    Set wb2 = Workbooks("client_list.xlsm")
    Set sh2 = wb2.Sheets("List")
    Set sh3 = wb2.Sheets("Reference")
    
    client = sh1.Range("G7")
    Set f = sh2.Range("A:A").Find(client, , xlValues, xlWhole)
    If f Is Nothing Then
      sh2.Range("A" & Rows.Count).End(xlUp)(2) = client
    End If
    
    Range("A2", Range("A2").End(xlDown)).Sort Key1:=Range("A2"), Order1:=xlAscending, header:=xlNo
    
    With sh3
        .Range("A2").End(xlDown).Copy .Cells(1, 0).Paste
    End With
    reference = sh3.Range("A2").End(xlDown).Value + 1
    
    With ActiveWorkbook
      .Save
    End With
  
End Sub

 

[DanteAmor]

I do not understand the code.


You can explain step by step what you do with the creation of the quotes.
And then explain step how you intend to add the reference.


Explain the 2 processes without using code, just use sheet and cell references and an example.

 

[dpaton05]

I worked it out thanks Dante.

 

[DanteAmor]

Good news, let me know if you have any questions.

 

[mikerickson]

Wouldn't it be wise to create a reference number by cocatenating the customer name and the date/time (e.g. SMITH_20190831_1423)that the quote is made. That would provide a unique reference number, but not require the customer to dig up some arbitrary number when calling customer support.

 

[dpaton05]

Thanks for the idea Mike. What would be the VBA to do this. At the moment, the name is not separated into first and last name, therefore, it may have spaces in the name. Clients also may not be a single person, such as Brown twins. So I can't really separate fields into last name to add to a reference number. What would be the vba to remove any spaces and add the date number behind it for a reference? The name is stored in G7 and the reference is stored in H5.

Thanks

 

[dpaton05]

Actually, don't worry about that as my supervisor doesn't want that feature, he is happy with just a number for the reference.

 

[mikerickson]

If you have a name, first, last, whatever, as long as it is unique to the customer, you can remove odd characters and replace spaces with underscores.

you could do something like

idString = Replace(Worksheetfunction.Trim(Replace(clientName,"'",""))," ","_") & "_" & Format(Now,"yyyymmdd_hhmm")

 

[dpaton05]

Thanks Mike, good to know.