...not as simple as I thought - or is it?

Posted by Stan Dubuque on January 31, 2002 12:31 PM

( simplified example )

I have a worksheet named "name/grade" with two columns of pertinent information, and "infinite" rows.
Column 1 is labeled "name", and column 2 is labeled "grade".
Grade value can be between 1 & 25

On a separate worksheet ( in the same workbook ) named Grade 1, I want a complete list of all names with grade level "1"

On a separate worksheet ( in the same workbook ) named Grade 2, I want a complete list of all names with grade level "1"
etc.etc.

And, if possible ( not critical), I would like to see the contents of the summary pages updated whenever the "name/grade" worksheet is modified.

Can this be done?

Posted by JohnG on January 31, 2002 1:52 PM

Try this
Sub GradeNameSort()
Dim FinalRow As Integer
Dim GNSloop As Integer
Dim Grd1CurRow As Integer
Dim Grd2CurRow As Integer

CurRow = 0
FinalRow = Sheets("Sheet1").Range("A65536").End(xlUp).Row

For GNSloop = 2 To FinalRow 'starts at 2 assuming there is a title in A1
If Sheets("Sheet1").Range("B" & GNSloop).Value = 1 Then 'if its grade=1
Grd1CurRow = Grd1CurRow + 1
Sheets("Sheet2").Range("A" & Grd1CurRow).Value = Sheets("Sheet1").Range("A" & GNSloop).Value
Sheets("Sheet2").Range("B" & Grd1CurRow).Value = Sheets("Sheet1").Range("B" & GNSloop).Value
ElseIf Sheets("Sheet1").Range("B" & GNSloop).Value = 2 Then 'if its grade=2
Grd2CurRow = Grd2CurRow + 1
Sheets("Sheet3").Range("A" & Grd2CurRow).Value = Sheets("Sheet1").Range("A" & GNSloop).Value
Sheets("Sheet3").Range("B" & Grd2CurRow).Value = Sheets("Sheet1").Range("B" & GNSloop).Value
End If
Next
End Sub

Posted by Stan Dubuque on January 31, 2002 3:18 PM

Thanks, John...EXACTLY what I was looking for.