Forum Discussion
Copy entire row to another sheet when either one of two criteria is met
- Jun 22, 2021
It's my fault - sorry about that.
I used "NonComformitySchedule" in the code instead of "NonConformitySchedule". Please change this in the line Set wsh = Worksheets(...)
Right-click the sheet tab of AuditChecklist.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module (Edited to correct typo😞
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsh As Worksheet
Dim rng As Range
If Not Intersect(Range("H10:H" & Rows.Count), Target) Is Nothing Then
Set wsh = Worksheets("NonConformitySchedule")
For Each rng In Intersect(Range("H10:H" & Rows.Count), Target)
Select Case rng.Value
Case "M", "R"
rng.EntireRow.Copy Destination:=wsh.Range("A" & rng.Row)
Case Else
' Do nothing
End Select
Next rng
Application.CutCopyMode = False
End If
End Sub
Switch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm) and make sure that you allow macros when you open it.
It is telling me that there is a Run-time error '424' and object required on the
" If Not Intersect(Range("H10........ line
thank you anyways for your time and effort, i am most grateful 🙂
Kindest regards
Richard
- HansVogelaarJun 22, 2021MVP
That is strange - the code works when I try it. Could you attach a copy of the workbook demonstrating the problem? Don't include any sensitive/proprietary information.
- LavalipsJun 22, 2021Copper Contributor
HI Hans,
Thank you for this, please find attached the workbook.
kindest regards
Richard
- HansVogelaarJun 22, 2021MVP
It's my fault - sorry about that.
I used "NonComformitySchedule" in the code instead of "NonConformitySchedule". Please change this in the line Set wsh = Worksheets(...)