Archive of Mr Excel Message BoardArchive of Mr Excel Message Board
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?
| Check out our Excel VBA Resources | ||||
 |
 |
 |
 |
 |
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