Macro for copy data from one sheet to another based on drop-down value

[joergeg]

Hi,

I want to be able to update my workbook when its opened by a macro copying all rows with a spesific dropdown value to the next sheet. This code has a debug i am not able to solve:

Private Sub Workbook_Open()
Dim i, LastRow

LastRow = Sheets("01. Check list").Range("A100").End(xlUp).Row
Sheets("02. Risk assesment").Range("A2:I500").ClearContents
For i = 2 To LastRow
If Sheets("01. Check list").Cells(i, "Risk").Value = "Yes" Then
Sheets("01. Check list").Cells(i, "Risk").EntireRow.Copy Destination:=Sheets("02. Risk assesment").Range("No." & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End Sub

A button in sheet1 doing the same would also be nice.

Any tips? Thanks in advance for all feedback.

[davesexcel]

Column A is not Column "No." nor is column D column"Risk"

    Dim i, LastRow

    LastRow = Sheets("01. Check list").Range("A100").End(xlUp).Row
    Sheets("02. Risk assesment").Range("A2:I500").ClearContents
    For i = 2 To LastRow
        If Sheets("01. Check list").Cells(i, "D").Value = "Yes" Then
            Sheets("01. Check list").Cells(i, "D").EntireRow.Copy Destination:=Sheets("02. Risk assesment").Range("A" & Rows.Count).End(xlUp).Offset(1)
        End If
    Next i

[joergeg]

Thanks Problem solved!