Error 424 Object required

[erutherford]

2 questions:

1. The code below worked fine as a "change event", but I decided to allow the user to edit the data then copy over to the "WO" sheet. However when I restructured it using a command button, the code gives a "Object Req" Error 424.

Private Sub CommandButton1_Click()

    If Not Intersect(Target, Range("H:H")) Is Nothing Then
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
  
Dim Lastrow As Long
    Lastrow = Sheets("WO").Cells(Rows.Count, "H").End(xlUp).Row + 2
    If Target.Value = "1" Or Target.Value = "2" Or Target.Value = "3" Then Target.Copy Destination:=Sheets("WO").Range("H" & Lastrow) 'Copies Priority Rating to Col H.
    If Target.Value = "1" Or Target.Value = "2" Or Target.Value = "3" Then Target.Offset(, -6).Copy Destination:=Sheets("WO").Range("A" & Lastrow) 'Copies Item to Col.A
    If Target.Value = "1" Or Target.Value = "2" Or Target.Value = "3" Then Target.Offset(, -5).Copy Destination:=Sheets("WO").Range("B" & Lastrow) 'Copies Issues to Col.B
    If Target.Value = "1" Or Target.Value = "2" Or Target.Value = "3" Then Target.Offset(, -4).Copy Destination:=Sheets("WO").Range("C" & Lastrow) 'Copies Est. to Col.C
    
   End If
   
End Sub

Question 2.

When the above code "crashes", I can reset it only by exiting excel completely. Shouldn't it be able to reset in the code window?

 

[erutherford]

yes it did, but for me it became very complex. Those that helped, explained the how's and why's so I do understand a little more about the code.

Here is what was the final solution looks like.

Private Sub CommandButton2_Click() 'Copies to WO sheet
  Dim R As Long, X As Long, Data As Variant, Result As Variant
  Data = Range("A1", Cells(Rows.Count, "H").End(xlUp))
  ReDim Result(1 To UBound(Data), 1 To 8)
  
 X = Sheets("WO").Range("A" & Rows.Count).End(xlUp).Row

For R = 1 To UBound(Data)
    If Data(R, 8) Like "[1-3]" Then
        X = X + 1
        With Sheets("WO")
            .Cells(X, 2).Value = Data(R, 2) 'Rpt-OI-Col.B to WO-Col.B
            '.Cells(X, 4).Value = Data(R, 3) 'Rpt-OI-Col.D to WO-Col.C
            '.Cells(X, 6).Value = Data(R, 8) 'Rpt-OI-Col.F to WO-Col.H
            '.Cells(X, 8).Value = Data(R, 8) 'Rpt-Chklst-Col.H to WO-Col.H
        End With
    End If
Next R
MsgBox "Data Transfered"
End Sub