Sleeper: Copy rows to worksheet two

[phdwsm]

I am trying to create an executive summary page of a audit spreadsheet. The first speadsheet contains various columns i.e audit question, level of compliance score ( The cell is conditional formatted to turn red if the number 1 or 2 is entered and orange if number 3 is entered) the last column is comments. I would like to copy rows which score 1,2 or 3 on to the second worksheet to create an executive summary page.

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "D:D" '<== change to suit
Dim sh As Worksheet
Dim NextRow As Long
On Error GoTo ws_exit
Application.EnableEvents = False
Set sh = Worksheets("Sheet2")
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If IsNumeric(.Value) Then
If .Value = .Value \ 1 Then
If .Value >= 1 And .Value < 4 Then
Select Case True
Case sh.Range("A1").Value = "": NextRow = 1
Case sh.Range("A2").Value = "": NextRow = 2
Case Else: NextRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row + 1
End Select
Me.Rows(.Row).Copy sh.Cells(NextRow, "A")
End If
End If
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[phdwsm]

thnak you for the reply. How to i get it to work. i have copied and pasted it in and edited it where you told me (D:D) now is (D20:D230)

[Bob Phillips]

So if you followed the instructions, enter a value in D20LD230 and check the other sheet.

[phdwsm]

Sorry i pasted the code to worksheet two. now pasted into worksheet one and it works.

Could you tell me where in the code i can enter that i want the whole row to be copied to worksheet two if the criteria has been met.

[Bob Phillips]

It already copies the whole row.

[phdwsm]

how do i now remove any blanks rows from worksheet two as they have not met the criteria and thus there was no need for them to be copied.

[Bob Phillips]

There shouldn't be any that don't match the criteria, that is the whole point of the code.

[phdwsm]

no i only score of 1 to 3 should be copied and row with a score of 4 will not be needed on the second sheet. I have changed the code where is says


If .Value >= 1 And .Value < 4 Then

to now read

If .Value >= 1 And .Value < 3 Then
is that ok?

but this will mean i will have blank rows on sheet two

[Bob Phillips]

I don't get it, why will you have blank rows?

[phdwsm]

you where right. i do not have blanks but i am not getting all the cells in the row from i.e A20 to G20