Move Rows To a Specific Worksheet Based on Entry in a Cell

[StarFL]

Hello - I've searched the forum, but not found anything that specifically addresses my issue.

I have a task list workbook that contains 4 sheets: Master, Today, Assigned and Done. All tasks are entered in Master, and I want to have the row deleted from Master and moved to the next available row on one of the other 3 worksheets based on the entry in column F - to Today if I enter a "T", Assigned if I enter "A" or Done if I enter "D".

I'm currently using code provided by My Aswer Is This (Thanks!) in response to another poster to move items from Master, Today and Assigned to Done. Works brilliantly and presumably still will for moving completed items from Today and Assigned to Done - but, I'd like to modify the code on the Master worksheet to move tasks to either T or A or D.

As my experience with VBA doesn't extend much further than copying and pasting code, I am hoping for the kind assistance of one more knowledgeable. The existing code is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)If Not Intersect(Target, Range("F:F")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Lastrow = Sheets("Done").Cells(Rows.Count, "F").End(xlUp).Row + 1


    If Target.Value = "D" Then
        Rows(Target.Row).Copy Destination:=Sheets("Done").Rows(Lastrow)
        Rows(Target.Row).Delete
    End If


End If
Exit Sub

End Sub

Thanks in advance for your help.

Star

 

[Osvaldo Palmeiro]

Hi.
This code will copy/paste range A:J ~~~> Resize(, 10) ~~~> change it to suit

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim ws As Worksheet
  If Target.Count > 1 Then Exit Sub
  If Target.Column <> 6 Then Exit Sub
   For Each ws In ThisWorkbook.Worksheets
    If Left(ws.name, 1) = UCase(Target.Value) Then
     Cells(Target.Row, 1).Resize(, 10).Copy ws.Cells(Rows.Count, 1).End(3)(2)
     Rows(Target.Row).Delete
     Exit Sub
    End If
   Next ws
End Sub

 

[StarFL]

Osvaldo - Thank you for your response! The items on the list are being moved to the proper sheet, but rather than being added on at the next available row, items are moved to the first row of the target sheet, replacing what was already there. Can you add code that moves the item to the next open row on the target sheet instead?

Thanks again - Star

 

[Osvaldo Palmeiro]

Hi.
Sorry, probably the column A of the target sheets is not the first column that contains data, that's where the code looks for the first empty row.
Please, what is the first column with data on the target sheets?

 

[StarFL]

The data on each worksheet begins in A4.

 

[StarFL]

There is a Title in A1, a blank row, column headings in A3:G3, then the actual data starting at A4.

 

[Osvaldo Palmeiro]

... column headings in A3:G3, ...

Then, the code is ok. First time the code will paste in A4:J4, the next time it will paste in A5:J5, and so on.

If range with data is A:G, then you could change to Resize(, 7)

 

[StarFL]

Unfortunately, it is still pasting all copied data to row 4 of the targeted worksheet.

 

[Fluff]

Will you always have data in col A?

 

[Osvaldo Palmeiro]

Try this (looking for the first empty row in column B, instead).

[COLOR=#652191][I]Cells(Target.Row, 1).Resize(, 7).Copy ws.Range("A" & ws.Cells(Rows.Count, 2).End(3)(2).Row)[/I][/COLOR]