I agree with what your implying ofcourse simple is always good, however, I have been trying to make this work and I'm actually almost done and my only problem left is that values like date for example is 16/8/19, values with decimal like 0.50 and time values like 7:00 AM is not being recognized as a duplicate.

Here's the transfer button's code that i come up with, with your help. (I also learned about combobox so I used it as a replacement for some textbox)

Edit: My sheet contains 12 columns and the 12th column is the one that has formula (product of column 10 * column 11)

Code:

Private Sub cmdTranfer_Click()
Dim ws As Worksheet, rng As Range, arr As Variant
Dim lr As Long, r As Long, c As Long
Dim textWS As String, textUF As String
Set ws = Sheets("Sheet1")
Set rng = ws.Range("A1").CurrentRegion
arr = rng
lr = UBound(arr)
Dim ctrl As Variant
For Each ctrl In Array(ComboBox1, ComboBox2, ComboBox3, TxtBox4, TxtBox5, TxtBox6, TxtBox7, ComboBox8, ComboBox9, TxtBox10, TxtBox11)
textUF = textUF & "|" & ctrl.Text
Next ctrl
For r = 1 To lr
textWS = ""
For c = 1 To rng.Columns.Count
textWS = textWS & "|" & arr(r, c)
If textWS = textUF Then GoTo SkipRowUpdate
Next
Next r
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
ws.Range("A" & LastRow).Value = ComboBox1.Text
ws.Range("B" & LastRow).Value = ComboBox2.Text
ws.Range("C" & LastRow).Value = ComboBox3.Text
ws.Range("D" & LastRow).Value = TxtBox4.Text
ws.Range("E" & LastRow).Value = TxtBox5.Text
ws.Range("F" & LastRow).Value = TxtBox6.Text
ws.Range("G" & LastRow).Value = TxtBox7.Text
ws.Range("H" & LastRow).Value = ComboBox8.Text
ws.Range("I" & LastRow).Value = ComboBox9.Text
ws.Range("J" & LastRow).Value = TxtBox10.Text
ws.Range("K" & LastRow).Value = TxtBox11.Text
Exit Sub
SkipRowUpdate:
If MsgBox("Enter This Data?", vbYesNo, "DUPLICATE OF ROW " & r) = vbYes Then
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
ws.Range("A" & LastRow).Value = ComboBox1.Text
ws.Range("B" & LastRow).Value = ComboBox2.Text
ws.Range("C" & LastRow).Value = ComboBox3.Text
ws.Range("D" & LastRow).Value = TxtBox4.Text
ws.Range("E" & LastRow).Value = TxtBox5.Text
ws.Range("F" & LastRow).Value = TxtBox6.Text
ws.Range("G" & LastRow).Value = TxtBox7.Text
ws.Range("H" & LastRow).Value = ComboBox8.Text
ws.Range("I" & LastRow).Value = ComboBox9.Text
ws.Range("J" & LastRow).Value = TxtBox10.Text
ws.Range("K" & LastRow).Value = TxtBox11.Text
End If
End Sub

## Like this thread? Share it with others