Adding a name to a list if it doesn't exist

[dpaton05]

I have a spreadsheet that is used to generate quotes for clients.

• The worksheet (npss_quote_sheet) is used to generate 1 quote for one client at a time.
• There is a table (npss_quote) that lists the services in the quote for the one client.
• Once all services have been entered, the quote is copied to another sheet (Costing_tool), to another table (tblCosting) for additional data entry

Within the process that copies from the table npss_quote to the table tblCosting, I need code that will take the client name that is stored in a merged cell of G7:H7 and add it (if it doesn't already exist) to a table called ChildYP, that is stored on the sheet List that is in the file client_list.xlsm that is stored in the same folder as the spreadsheet file.

Can someone help me with the vba code please?

 

[DanteAmor]

Try this.

I recommend you to open the book "client_list"
Adjust the data in red for your information.

Sub Adding_name()
  Dim wb1 As Workbook, wb2 As Workbook
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim f As Range, client As Variant
  
  Set wb1 = ThisWorkbook
  Set sh1 = wb1.Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
  Set wb2 = Workbooks("[COLOR=#ff0000]client_list[/COLOR].xlsm")
  Set sh2 = wb2.Sheets("[COLOR=#ff0000]List[/COLOR]")
  
  client = sh1.Range("[COLOR=#ff0000]G7[/COLOR]")
  Set f = sh2.Range("[COLOR=#ff0000]A:A[/COLOR]").Find(client, , xlValues, xlWhole)
  If f Is Nothing Then
    sh2.Range("[COLOR=#ff0000]A[/COLOR]" & Rows.Count).End(xlUp)(2) = client
  End If
End Sub

 

[dpaton05]

Thanks for that Dante, it pasted the name in the cell below where the table is, not in the table. Is it better to use a range for this or a table?

I did modify my spreadsheet to work with just a range.

 

[dpaton05]

I remember now that I made the table when i couldn't get the range working so I had no reference to the new table in the original post. I will just keep it as a range so it is working perfectly thankyou!!

 

[DanteAmor]

I remember now that I made the table when i couldn't get the range working so I had no reference to the new table in the original post. I will just keep it as a range so it is working perfectly thankyou!!

I'm glad to help you. Thanks for the feedback.