Hi,


I have a userform for entry in/out movements of items however, the coding for out movements in my userform seem to have an issue of duplicate of entries in my range cells. My range cells is listed in worksheet =Movements!$A$2:$A$102 whereby when I entered another record and sort by item code there will be double entry in my listbox.

I want to keep the previous record and update a new entry in my list box, how can I do that? I have use offset codes but missing the error show as "Copy method class range failed"

Below is my code


Private Sub CommandButton1_Click()
Dim iRow As Long
Dim rng As Range
Dim ws As Worksheet
Set ws = Worksheets("Movements")
'''find  first empty row in database
''iRow = ws.Cells(Rows.Count, 1) _
''  .End(xlUp).Offset(1, 0).Row
'revised code to avoid problems with Excel tables in newer versions
iRow = ws.Cells.Find(What:=Me.ListBox1.List(Me.ListBox1.ListIndex), SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row
        
  With ws
  .Range("B2:B60").Offset(1).Copy (.Cells(iRow, 2).Value = Me.TextBox2.Value)
  .Range("C2:C60").Offset(1).Copy (.Cells(iRow, 3).Value = Me.TextBox3.Value)
  .Range("D2:D60").Offset(1).Copy (.Cells(iRow, 4).Value = Me.TextBox7.Value)
  .Range("E2:E60").Offset(1).Copy (.Cells(iRow, 5).Value = Me.TextBox4.Value)
  .Range("F2:F60").Offset(1).Copy (.Cells(iRow, 6).Value = Me.TextBox5.Value)
  .Range("G2:G60").Offset(1).Copy (.Cells(iRow, 7).Value = Me.TextBox9.Value)
  .Range("I2:I60").Offset(1).Copy (.Cells(iRow, 9).Value = Me.TextBox8.Value)
  End With

'clear the data
    Me.TextBox1.Value = ""
    Me.TextBox2.Value = ""
    Me.TextBox3.Value = ""
    Me.TextBox4.Value = ""
    Me.TextBox5.Value = ""
    Me.TextBox9.Value = ""
    Me.TextBox7.Value = ""
    Me.TextBox8.Value = ""
    Me.ListBox1.SetFocus
    
    
End Sub

Appreciate anyone who can help me on this, thanks


Imzhakmaya