Macro to cut and paste based on cell value

[doublej41]

Hi All,

I am looking for some help, with what I assume will require a macro.

I currently have a list of tasks on sheet 1 that are labelled as Open, In Progress or Closed using data validation in column K. When Closed is selected I would like that row to be cut and paste to the next available row on sheet 2.

Is there a straightforward way to get this working with a macro?

All responses will be gratefully received.

Jonathan

 

[nigelk]

Hi

Assuming you only want to cut columns A-J and not the data validation itself, put this in as a worksheet change event

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim LastR As Long, RowNum As Long

    If Selection.Count > 1 Then Exit Sub

    If Target.Column <> 11 Then Exit Sub

    If Target.Value = "Closed" Then

    RowNum = Target.Row


    LastR = Sheets("Sheet2").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


  

    ActiveSheet.Range(Cells(RowNum, 1), Cells(RowNum, 10)).Cut _
    Destination:=Sheets("sheet2").Cells(LastR + 1, 1)

    End If


End Sub

 

[doublej41]

Hi nigelk

Many thanks for your reply.

When using the code I get the error "Run-time error 91: Object variable or With block variable not set".

When I click debug, it highlights the following row in yellow:

LastR = Sheets("Sheet2").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Any suggestions as to why this might be? As you can probably sense, I am not too clued up when it comes to macros.

 

[nigelk]

Sorry, Just make sure that the first row has column headers before running.

Nigel.

 

[doublej41]

After adding column headings, I am still getting the same error.

Do I need to change any of the code to match my workbook? All sheets still have their default names.