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

 

[VoG]

Maybe Excel Explosion will help http://www.datapigtechnologies.com/freeware.htm

This will create the class tabs and will error if they already exist.

 

[Barry Katcher]

Thanks, Peter - I'll check it out when I get home this evening.

Barry

 

[Barry Katcher]

I installed Excel Exposion and tried it - it didn't create any new tabs.

In any event, even if it worked, it would only do the initial set-up. If I need to edit the book after it's set up - for example, move a kid from one class to another - I still have to do it manually.

There must be a formula that I can place in the Class tabs that will pull from the Master, based on the data in the Class column - I just don't know what it would be. Can't use a VLOOKUP, that will only pull the first row on the Master list.

_Denise_VLOOKUP_VBS.xls
	A	B	C	D
2	MasterList	Allstudents
3
4	Name	address	City	Class
5	Adams,Frank	xx	xx	2
6	Clayton,Gail	xx	xx	4
7	Dead,Osama	xx	xx	2
8	Dog,Smiley	xx	xx	2
9	Fenwick,Iris	xx	xx	4
10
11
12	Classes-flows	fromMaster
13
14	Class2
15	Adams,Frank	xx	xx	2
16	Dead,Osama	xx	xx	2
17	Dog,Smiley	xx	xx	2
18
19	Class4
20	Clayton,Gail	xx	xx	4
21	Fenwick,Iris	xx	xx	4
22
23	andsoon
Sheet1

 

[VoG]

Try this: it assumes that the master sheet is called Master and that you have existing sheets called "Class 1" and so on

Sub classes()
Dim LR As Long, i As Long
With Sheets("Master")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LR
        .Rows(i).Copy Destination:=Sheets("Class " & .Range("D" & i).Value).Range("A" & Rows.Count).End(xlUp).Offset(1)
    Next i
End With
End Sub

 

[Barry Katcher]

Peter, let me see if I understand what this macro does. From what I can guess, every time the Master list is edited, the macro is run and it refreshes all of the Class tabs. Is this correct?

If so, please clarify a couple of lines, as the sample I posted is a simplified version:

1) On the actual Master tab and the Class tabs, the classes are in column R, not column D. On line 6, "......& .Range("D" & i).Value....", does the D stand for column D on the destination tabs?

2) Will this macro refresh ALL of the data on the Class tabs, and not just the Class columns?

This workbook is going to be handed over to another person, with absolutely no macro abilities. I can put in a button to run the macro, but I still wish there was a formula I could use, as opposed to a macro.

Thanks,

Barry

 

[VoG]

OK, try this. Each time it is run it will

Clear the contents of the Class sheets, leaving row 1 intact (for headers)

Copy rows to the relevant Class sheet depending on the number in column R.

Sub classes()
Dim LR As Long, i As Long, ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    If ws.Name Like "Class *" Then
        ws.UsedRange.Offset(1).ClearContents
    End If
Next ws
With Sheets("Master")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LR
        .Rows(i).Copy Destination:=Sheets("Class " & .Range("R" & i).Value).Range("A" & Rows.Count).End(xlUp).Offset(1)
    Next i
End With
End Sub

 

[Barry Katcher]

O.K., Peter, I'll give it a try, probably tomorrow morning.

Incidentally, each of my Class tabs use the first three rows for titles, the column headers are in row 4, with the data beginning in row 5. Do I change "For i = 2 To LR" to "For i = 5 To LR"?

Thanks,

Barry

 

[VoG]

No but we'll need the change in red

Sub classes()
Dim LR As Long, i As Long, ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    If ws.Name Like "Class *" Then
        ws.UsedRange.Offset(4).ClearContents
    End If
Next ws
With Sheets("Master")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LR
        .Rows(i).Copy Destination:=Sheets("Class " & .Range("R" & i).Value).Range("A" & Rows.Count).End(xlUp).Offset(1)
    Next i
End With
End Sub

Change 2 to the row on the Master sheet where the data begins.

 

[Barry Katcher]

Gotcha, Peter. Let you know tomorrow if it works.

Thanks,

Barry