What is the best way to do this?

[htchandler]

Rows are each individual, Columns are information about each individual. In one of those column's I'm going to have a drop down menu to select "Present", "Absent", "Training", etc. When you select any one of these I want something to cause some of that individual's information to be copied over to a seperate tab to build a roster of all those that are "Present", "Absent", etc. What do I use to cause each individual's information to be copied to a seperate tab?

 

[VoG]

This can be done using VBA event code. Which column will the data validation be in?

 

[htchandler]

By data validation do you mean what colum will the drop down be in? If so it will probably not be in the left most column of the spreadsheet. Does it make a difference what column the data validation is in?

 

[VoG]

If you want a specific solution, yes.

 

[htchandler]

For now I think I actually will be placing the status in Column A. Have any good advice for books or training materials for VBA?

 

[VoG]

This assumes that you have sheets already set up named exactly the same as your drop down list, i.e Present, Training etc.

Right click the tab of your 'master' sheet, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then 'this is for column A, for column E use 5 and so on
Target.EntireRow.Copy Destination:=Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub

For learning VBA see Hiker's list http://www.mrexcel.com/forum/showpost.php?p=2676997&postcount=5

 

[htchandler]

I'm receiving an error:

"Block end if without if"

Is there a way I can paste a screen shot of the sheet I'm working with on this forum so you can better see my set up?

 

[Rick Rothstein]

It looks like Vog omitted an End If statement. Try this modification to Vog's code to see if it works...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 1 Then 'this is for column A, for column E use 5 and so on
    Target.EntireRow.Copy Destination:=Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Offset(1)
  End If
End Sub

 

[htchandler]

Wow it works. Thank you. There is just a couple of modifications I need now:

I get a run time error if I change the status from anything (e.g. "Present") to blank. I would just like it to remove the individuals name from the roster they were on and let me leave their status blank.

Right now once I make a status selection for a person their name stays on that roster even if I change their status. What do I need to do to the code to make it so if I select "Present", then "Absent" for 1 individual so their name would be pulled off the Present Roster and moved over to the Absent Roster.

Also, what do I need to do to the code so that it only copies the data in rows B, C, D, and F (E is their SSN and I don't need that posted to the roster of each group)

P.s. What do you recommend for somone just learning code for the first time? I've been looking through books all weekend but they all just want to teach me how to make a window pop up that says "Hello World".

Thank you again for the help. This will significantly reduce my offices workload.

 

[VoG]

To fix the error and copy only columns B,C,D and F try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Value <> "" Then 'this is for column A, for column E use 5 and so on
    Target.Offset(, 1).Resize(, 3).Copy Destination:=Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Offset(1)
    Target.Offset(, 5).Copy Destination:=Sheets(Target.Value).Range("D" & Rows.Count).End(xlUp).Offset(1)
End If
End Sub

Pulling a person off one sheet and onto another would be difficult unless they can physically only belong to one sheet at a time. If that is the case then their SSN would be useful as a unique identifier.