Master list to sub-lists

[Barry Katcher]

Hello, people.

I have a tab with a master list of all the students in my school, including a column with their class. I have ten tabs for the individual classes. Can someone help me with a formula to use in the individual class tabs to bring over the student's name from the master list and place it in the appropriate class tab? Each class has between 4 and 12 kids in it, all of them on the master list.

All I need is the name - once I have that, I can use a VLOOKUP to bring over all of their other information.

Thanks,

Barry

 

[Barry Katcher]

Peter - I set up my tabs, imported your macro, and got an error message:
Run-time error '9', Subscript out of range.

When I de-bugged, it was hung up on this line:
.Rows(i).Copy Destination:=Sheets("Class " & .Range("R" & i).Value).Range("A" & Rows.Count).End(xlUp).Offset(1)

The master list tab is named "Master" (without the quotes), and all of the class tabs begin with "Class", followed by a space.

The sample below is the actual layout of the Master and of the Classes. I did hide some columns in the center so it will display on the screen.

When run, all the info from the Master, col A:Q, starting with line 2, should transfer to its respective class tab, starting in row 5, determined by the class number in col. R of the Master.

What do you think needs fixing in the macro?

Thanks, Barry

_Denise_VLOOKUP_VBS_test_macro.xls
	A	B	C	D	E	F	G
10		Tabname"Master"		Allinfofrom	MastershouldflowtoClass
11		A	B	C	Q	R	S
12	1	(ThisiscellA1)	LastName	FirstName	ChurchName	Class	ClassName
13	2	Acevedo,Ethan	Acevedo	Ethan	-	1-b	Chillin'Outers
14	3	Acevedo,Gabriel	Acevedo	Gabriel	-	5-a	Surfers
15	4	Adams,David	Adams	David	St.AndrewsPres.	3-a	SkinDivers
16	5	Adams,Lindsey	Adams	Lindsey	St.AndrewsPres.	6-a	WipeOuters
17	6	Agate,Gage	Agate	Gage	Faith	K-b	WaterSplashers
18
19
20		Tabname"Class5-aSurfers"
21		A	B	C	Q
22	1	(ThisiscellA1)	Surfers
23	2		Sandy	Allison
24	3		Student
25	4	Key	LastName	FirstName	ChurchName
26	5
27	6
28	7
Sheet2

 

[VoG]

That means that one of the Class sheets does not exist. You'll need to carefully check the spelling of each one (for example no leading or trailing spaces in the sheet name).

 

[Barry Katcher]

Aha! I do have several other tabs, other than the Master and the various classes, such as Teachers and Instructions. Could that be it?

If so, can I correct that by renaming Teachers to Class Teachers and Instructions to Class Instructions?

Also, I'm not clear on how the macro knows which rows to take from the Master and put them into their proper tab. Could you possibly explain that to me? Do I have to change the data in column R on the Master from, say, 5-a to Class 5-a or to Class 5-a Surfers , or is that not necessary?

Thanks for your patience, Peter.

Barry

 

[VoG]

It loops down the Master sheet, looks at column R for the class name then tries to copy that whole row to the Class sheet. So if it finds 5-a in column R it tries to copy that row to a sheet called "Class 5-a" (without the quotes).

If the value in column R contains leading or trailing spaces, it will fail.