method range of object _worksheet failed

[Alrabia]

Im having a runtime error on my code below. can someone please help me.

Thank you very much.

Private Sub CmdDelete_Click()

'find the row
Set findvalue = DataSH.Range("B:B").Find(What:=Me.Emp1.Value, _
LookIn:=xlValues, LookAt:=xlWhole)

'delete the entire row
Set Addme = Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
findvalue.Range("A1:K1").Copy
Addme.PasteSpecial (xlPasteValues)

'delete the entire row
findvalue.EntireRow.Delete
End If

 

[RoryA]

On which line?

 

[Alrabia]

On which line?

i dont know. i try to debug it and theres no error. but when i try to click the command button the error is "method range of object _worksheet failed". below is my full code

Private Sub cmdDelete_Click()
'declare the variables
Dim findvalue As Range
Dim cDelete As VbMsgBoxResult
Dim cNum As Integer
Dim DataSH As Worksheet
Set DataSH = Sheet1
Dim x As Integer
Dim Addme As Range
'error statement
On Error GoTo errHandler:
'hold in memory and stop screen flicker
Application.ScreenUpdating = False
'check for values
If Emp1.Value = "" Or Emp2.Value = "" Then
MsgBox "There is not data to delete"
Exit Sub
End If
'give the user a chance to change their mind
cDelete = MsgBox("Are you sure that you want to delete this training", _
vbYesNo + vbDefaultButton2, "Are you sure????")
If cDelete = vbYes Then
'find the row
Set findvalue = DataSH.Range("B:B").Find(What:=Me.Emp1.Value, _
LookIn:=xlValues, LookAt:=xlWhole)
'delete the entire row
Set Addme = Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
findvalue.Range("A1:K1").Copy
Addme.PasteSpecial (xlPasteValues)
'delete the entire row
findvalue.EntireRow.Delete
End If
'clear the controls
cNum = 7
For x = 1 To cNum
Me.Controls("Emp" & x).Value = ""
Next
'unprotect all sheets for the advanced filter
Unprotect_All
'filter the data
DataSH.Range("B8").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("Data!$M$8:$M$9"), CopyToRange:=Range("Data!$O$8:$Y$8"), _
Unique:=False
'if no data exists then clear the rowsource
If DataSH.Range("M9").Value = "" Then
lstEmployee.RowSource = ""
Else
'add the filtered data to the rowsource
lstEmployee.RowSource = DataSH.Range("outdata").Address(external:=True)
End If
'sort the data by "Surname"
DataSH.Select
With DataSH
.Range("B9:L30000").Sort Key1:=Range("E9"), Order1:=xlAscending, Header:=xlGuess
End With
'Protect all sheets
Protect_All
'return to sheet
Sheet2.Select
'error block
On Error GoTo 0
Exit Sub
errHandler:
'Protect all sheets if error occurs
Protect_All
'show error information in a messagebox
MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " & _
Err.Number & vbCrLf & Err.Description & vbCrLf & "Please notify the administrator"
End Sub

 

[RoryA]

I'm going to guess that your button is on a worksheet? If so, (and as a general rule, in my opinion) you should not use references like Range("Data!$O$8:$Y$8"). Replace them with fully qualified worksheet/range combinations like Sheets("Data").Range("O8:Y8").

 

[Alrabia]

my command button is in userform. the name of my worksheet is Data.

 

[RoryA]

Then I suggest you comment out the On Error GoTo errHandler: line and run the code again. I do note that this:

With DataSH
.Range("B9:L30000").Sort Key1:=Range("E9"), Order1:=xlAscending, Header:=xlGuess
End With

should look like this:

With DataSH
.Range("B9:L30000").Sort Key1:=.Range("E9"), Order1:=xlAscending, Header:=xlGuess
End With

Note the extra dot.