Copy rows from one Master work sheet to multiple worksheets

[JJgold]

We have a spreadsheet of all our client's trademarks and relevant information. All the information is contained in a worksheet titled "All."

I would like the information contained in the rows from "All" to be copied to the applicable worksheet based on values in "All" column "E" and column "I." The information in column "E" will be placed onto one of two worksheets "1a" and "1b" and if there is data in column "I" it would copy to worksheet entitled "Registered."

So if column "E" in "ALL" says 1a it will copy the data to worksheet "1a" and if the data says 1b it will copy to worksheet "1b." Here's the kicker....If column "I" contains any data then I want it to copy to "Registered" and have the formula or macro ignore the information in column "E" OR have it copy to both the relevant "1a" or "1b" worksheet but also copy to "Registered." Either way will work so which every is easiest.

Can I do this with a formula? I know very little about macros (or for that matter anything more advanced than filter sorting....I'm very new to Excel.

Thank you for your assistance.

 

[mumps]

Do you want this to happen automatically as you enter the data in each row, or do you want to do the copying after you have entered all the data in all the rows by running a macro?

 

[JJgold]

It would be cool if it was copied as I was typing so it would update cells I need to update but if that creates complications than I can run the macro at the end of each day.

Thank you for your quick response.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your "All" 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. The macro is triggered by a change in any cell in column E so this must be the last column completed. For example, assuming that row 1 has the column headers, start in row 2 by entering your data in all the columns, leaving column E for last. When you enter "1a" in column E, that row will be copied to the "1a" sheet provided that the corresponding cell in column I has no data. When you enter "1b" in column E, that row will be copied to the "1b" sheet provided that the corresponding cell in column I has no data. If column I has data, the row will be copied to the "Registered" sheet regardless of what you enter in column E. Make sure that the sheets "1a", "1b" and "registered already exist.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("E:E")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    If Target.Offset(0, 4) = "" Then
        If Target = "1a" Then
            Target.EntireRow.Copy Sheets("1a").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        ElseIf Target = "1b" Then
            Target.EntireRow.Copy Sheets("1b").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    ElseIf Target.Offset(0, 4) <> "" Then
        Target.EntireRow.Copy Sheets("Registered").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
    Application.ScreenUpdating = True
End Sub

 

[JJgold]

Mumps, Thank you so so so so much. Excellent. Really appreciate the help.

 

[JJgold]

Mumps, it worked once but I am getting an error on the line in blue text below. Also, will this work for data that is already in the "All" worksheet?

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E:E")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
If Target.Offset(0, 4) = "" Then
If Target = "1a" Then
Target.EntireRow.Copy Sheets("1a").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
ElseIf Target = "1b" Then
Target.EntireRow.Copy Sheets("1b").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
ElseIf Target.Offset(0, 4) <> "" Then
Target.EntireRow.Copy Sheets("Registered").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
Application.ScreenUpdating = True
End Sub

 

[JJgold]

The exact error message is "Run time error 9" Script out of Range.

 

[JJgold]

Mumps. Nevermind. I made a mistake. One of the tab worksheet names was wrong. I fixed it and you remain brilliant! Thanks again. Sorry about all my messages.

 

[mumps]

My pleasure.