I use an Excel spreadsheet to create a report of clients I want to visit when I’m selling on the road. It consists of my list of all customers on one sheet and another sheet which I print or use for my visits. I use a simple vlookup function and copy and paste the account number at the front of the sheet and it populates the address and contact details etc. All simple and easy.
No what I want to do is when I go to my list of customers and click on the customer number I would like the customer number to automaticaly be pasted into the visit report to save time. The vllookup function can perform in the normal way. The function will also need to allow for the fact that the customer number will need to be pasted into the next clear cell down the respective column.
I have sent an example and look forward to hearing from anyone who can help.
Hi Steve
See attached, select the client ref you want to copy over then click the button (you can be anywhere in the row) - the first button adds the client ref to Visit report but allows dups, the second one doesn't allow dups - i assume you don't want duplicate visits? Just delete the button you don't want.
If you are doing to run a macro i would remove the vlookup and just get the macro to copy over the details, but not really an issue either way.
Sub UpdateVisitReportNoDups()
Dim vrlastRow As Integer
Dim clCurrentRow As Integer
Dim cr As String
Worksheets("Customer List").Activate
clCurrentRow = ActiveCell.Row
vrlastRow = Worksheets("Visit Report").Cells(Rows.Count, "b").End(xlUp).Row
If Range("a" & clCurrentRow) = "" Then
MsgBox "The row appears to be empty, please select a populated row", vbCritical, "Error"
Exit Sub
End If
cr = Range("a" & clCurrentRow)
If Worksheets("Visit Report").Range("b:b").Find(What:=(cr)) Is Nothing Then
Worksheets("Visit Report").Range("b" & vrlastRow + 1) = cr
Else: MsgBox "This client is already on the Visit Report", vbCritical
Exit Sub
End If
End Sub
