Populating multiple sheets based on Master sheet with Criteria

Closed
Report
Posts
2
Registration date
Wednesday September 18, 2013
Status
Member
Last seen
September 18, 2013
-
Hi,

I have a sheet "Master" with a column "Type" at its first column (Col A). It has drop down list and value can be - Email, Page, Abend and Ticket. I need to populate data on each individual type's sheet automatically when it's inputted onto the main "Master" sheet. Main sheet has col A to col K and I need all the column values to be copied into respective child sheet.

Using below query I got two bugs. Can anyone of you please help me to solve it ..

1) When I'm selecting the Type (@ col A) in Master sheet for a new record, a row with that type is being populated in respective child sheet. But afterwards when I'm putting values for col B to K in Master, those values are not being populated in child sheet. Col B to col K in child sheet remains blank even after I enter value at Master sheet for those columns. How to resolve it? I can't move col A at the last as per requirement.

2) What happen if someone copies the values (@ col A to K) in Master sheet with proper format? Even though the Type entered is correct (in drop down list values) .. the record is not being populated in respective child sheet.

Here is my query -

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("A:A")) Is Nothing Then Exit Sub

If Target.Value = "Page" Then
Range(Range("A" & Target.Row), Range("K" & Target.Row)).Copy _
Sheets("Page").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

End If

If Target.Value = "Abend" Then
Range(Range("A" & Target.Row), Range("K" & Target.Row)).Copy _
Sheets("Abend").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

End If

If Target.Value = "Email" Then
Range(Range("A" & Target.Row), Range("K" & Target.Row)).Copy _
Sheets("Email").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

End If

If Target.Value = "Ticket" Then
Range(Range("A" & Target.Row), Range("K" & Target.Row)).Copy _
Sheets("Ticket").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

End If

End Sub