Checking Duplication before pasting the data or POP UP before pasting the data

[shahzeb123]

I am using below code to copy data from one sheet to another. What i need is that before pasting there should be a pop up to say yes or no or it should check the duplication before pasting the data.

Dim R&
        R = Sheet6.Cells(Rows.Count, 2).End(xlUp)(2).Row
        Sheet6.Cells(R, 12).Value = Sheet1.[F24].Value
        Sheet6.Cells(R, 2).Value = Sheet1.[D14].Value
        Sheet6.Cells(R, 3).Value = Sheet1.[D15].Value
        Sheet6.Cells(R, 4).Value = Sheet1.[D16].Value
        Sheet6.Cells(R, 5).Value = Sheet1.[D20].Value
        Sheet6.Cells(R, 5).Value = Sheet1.[D20].Value
        Sheet6.Cells(R, 6).Value = Sheet1.[D21].Value
        Sheet6.Cells(R, 7).Value = Sheet1.[M1].Value
        Sheet6.Cells(R, 8).Value = Sheet1.[D5].Value
        Sheet6.Cells(R, 9).Value = Sheet1.[G15].Value
        Sheet6.Cells(R, 10).Value = Sheet1.[G16].Value
        Sheet6.Cells(R, 11).Value = Sheet1.[G19].Value
        Sheet6.Cells(R, 13).Value = Sheet1.[G43].Value
        Sheet6.Cells(R, 14).Value = Sheet1.[G44].Value
        Sheet6.Cells(R, 15).Value = Sheet1.[G45].Value
        Sheet6.Cells(R, 16).Value = Sheet1.[G46].Value
        Sheet6.Cells(R, 17).Value = Sheet1.[D35].Value

 

[DanteAmor]

How do you know if there is a duplicate, that is, in which column it should be checked?
And if the duplicate exists, what should the code do?

 

[shahzeb123]

D14 should be checked whether it is the duplicate or not.

If it is duplicate then pop up should be generated to ask whether to add or not

 

[DanteAmor]

Try this:

Sub test()
  Dim R&
  Dim f As Range
  
  With Sheet6
    Set f = .Range("B:B").Find(Sheet1.[D14].Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      If MsgBox("Add or not?", vbQuestion + vbYesNo, "DUPLICATION") = vbNo Then
        Exit Sub
      End If
    End If
    R = .Cells(Rows.Count, 2).End(xlUp)(2).Row
    .Cells(R, 12).Value = Sheet1.[F24].Value
    .Cells(R, 2).Value = Sheet1.[D14].Value
    .Cells(R, 3).Value = Sheet1.[D15].Value
    .Cells(R, 4).Value = Sheet1.[D16].Value
    .Cells(R, 5).Value = Sheet1.[D20].Value
    .Cells(R, 5).Value = Sheet1.[D20].Value
    .Cells(R, 6).Value = Sheet1.[D21].Value
    .Cells(R, 7).Value = Sheet1.[M1].Value
    .Cells(R, 8).Value = Sheet1.[D5].Value
    .Cells(R, 9).Value = Sheet1.[G15].Value
    .Cells(R, 10).Value = Sheet1.[G16].Value
    .Cells(R, 11).Value = Sheet1.[G19].Value
    .Cells(R, 13).Value = Sheet1.[G43].Value
    .Cells(R, 14).Value = Sheet1.[G44].Value
    .Cells(R, 15).Value = Sheet1.[G45].Value
    .Cells(R, 16).Value = Sheet1.[G46].Value
    .Cells(R, 17).Value = Sheet1.[D35].Value
  End With
End Sub

 

[shahzeb123]

It works great !!

Thank you so much for your valuable response. Much appreciated.

 

[shahzeb123]

needed a little more help on the same thing if u are able to respond so please.

What i want is to start my specific code as well in this. i.e when I click on it, it should paste "001/FID/SUMB" on A coulmn and it goes on "002/FID/SUMB' and so on

 

[DanteAmor]

Try this:

Sub test()
  Dim R&
  Dim f As Range
  
  With Sheet6
    Set f = .Range("B:B").Find(Sheet1.[D14].Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      If MsgBox("Add or not?", vbQuestion + vbYesNo, "DUPLICATION") = vbNo Then
        Exit Sub
      End If
    End If
    R = .Cells(Rows.Count, 2).End(xlUp)(2).Row
    .Cells(R, 1).Value = Format(R - 1, "000") & "/FID/SUMB"
    .Cells(R, 12).Value = Sheet1.[F24].Value
    .Cells(R, 2).Value = Sheet1.[D14].Value
    .Cells(R, 3).Value = Sheet1.[D15].Value
    .Cells(R, 4).Value = Sheet1.[D16].Value
    .Cells(R, 5).Value = Sheet1.[D20].Value
    .Cells(R, 5).Value = Sheet1.[D20].Value
    .Cells(R, 6).Value = Sheet1.[D21].Value
    .Cells(R, 7).Value = Sheet1.[M1].Value
    .Cells(R, 8).Value = Sheet1.[D5].Value
    .Cells(R, 9).Value = Sheet1.[G15].Value
    .Cells(R, 10).Value = Sheet1.[G16].Value
    .Cells(R, 11).Value = Sheet1.[G19].Value
    .Cells(R, 13).Value = Sheet1.[G43].Value
    .Cells(R, 14).Value = Sheet1.[G44].Value
    .Cells(R, 15).Value = Sheet1.[G45].Value
    .Cells(R, 16).Value = Sheet1.[G46].Value
    .Cells(R, 17).Value = Sheet1.[D35].Value
  End With
End Sub

 

[shahzeb123]

It is working great!!!

Thank you so much ! Really appreciate the effort

 

[DanteAmor]

Again with pleasure. Thanks for the feedback

 

[jphina]

Hi Dante, would be be able to assist me with a similar task? I would like to accomplish the same but I do not need a yes/no pop up, I simply want to check if the value is already listed in the range, and if so, pop up message saying "Associate already assigned". Also, if you have any tips on how to optimize my current code, that you be great too