Copying rows from one sheet to another based on specific term

[vertigoes]

Hi all, I've been trying to piece this together but my experience is limited -- thought I could accomplish this with a simple formula initially, but now not sure. Appreciate any help.


I have a workbook that lists jobs for a company that has 3 divisons.


I need to start maintaining separate sheets per division, but also need to maintain my master sheet of all jobs. So I would like rows relating to specific divisions to automatically get copied into their respective sheets -- so I only ever need to update my master sheet.


E.g.


IF a row contains "Dental" in Column B, that row content is copied to the Dental sheet


IF a row contains "Medical" in Column B, that row content is copied to the Medical sheet.

Thank you!!

 

[mumps]

I am assuming that all the necessary Division sheets already exist. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your Master sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. After you have entered all the data for each Job#, place an "X" in column I in that row and exit the cell. The row will be automatically copied to the appropriate Division sheet.
Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
    If Target = "X" Then
        Target.EntireRow.Copy Sheets(Target.Offset(0, -7).Value).Cells(Sheets(Target.Offset(0, -7).Value).Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
End Sub

 

[vertigoes]

Thanks! Works beautifully. Only one thing -- what if I want the rows copied regardless of whether they're completed? Ideally each division sheet would show all open projects for that division, both closed and open. Thanks again.

 

[mumps]

You are very welcome. When you enter the "X" whatever data you have entered in that row will be copied over. If data is missing in any column, the blank cell will be copied over.

 

[vertigoes]

Gotcha! Ok bear with me, just one more tweak please... I think I'd need to make an additional column just for this purpose, if the copying-over will be triggered by a keystroke in a specific column --- because that DONE column is already serving a different purpose. I need the rows to be copied over whether or not there is an X in column I (aka whether or not the job is marked as Done).

So if i add a column -- I'll call it MIGRATE? -- and change the range to J:J... then that'd be a fine fix. But how would I adjust the rest of the macro? I figured I could just change the column offset to -8 but that clearly ain't it

Thanks again -- so close!

 

[mumps]

This should do it.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("I:I,J:J")) Is Nothing Then Exit Sub
    If Target.Column = 9 And Target = "X" Then
        Target.EntireRow.Copy Sheets(Target.Offset(0, -7).Value).Cells(Sheets(Target.Offset(0, -7).Value).Rows.Count, "A").End(xlUp).Offset(1, 0)
    ElseIf Target.Column = 10 And Target = "X" Then
        Target.EntireRow.Copy Sheets(Target.Offset(0, -8).Value).Cells(Sheets(Target.Offset(0, -7).Value).Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
End Sub