Help! Can't get macro to work

[zbell]

I'm just learning macros in excel and I can't seem to get a certain macro to work. I have one sheet named "Master" that has all the data for the macro on it (donor and proposal information). Column A contains several campaigns in which this donor information is linked to. I would like the macro to pull the rows associated Campaigns in column A and place them on separate sheets (a separate sheet for each campaign). Thank you and I appreciate any help! This is the code I code I've created (with help from internet) and can't seem to get working.

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

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

ElseIf Target.Value = "Individual" Then
Range(Range("A:A" & Target.Row)).Copy _
Sheets("Individual").Range("A:A" & Rows.Count).End(xlUp).Offset(1, 0)

End If

End Sub

 

[zbell]

To clarify, my end goal is to drop updated data into the "Master" sheet, then have all the sheets in this workbook update automatically so that I don't have to create a separate document for each campaign every time I need to run these reports. I might be totally off-track here and making this more complicated than it needs to be by not using VLookup, idk.

 

[NoSparks]

Without knowing what your end goal really is.... I'm thinking just filter Master column A.

If you really need those other sheets, using Worksheet_Change you'll probably need to adjust it to be looking at the range of data on the row to know if it is ready to be copied to the other sheets.

Can you define what "drop updated data into the Master sheet means and how that's done ?

 

[mumps]

Try:

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

When you enter the word "Corporate" or the word "Individual" in column A and exit the cell, that row will be automatically copied into the appropriate sheet.