Use the code below. It needs to go into the code module for your "Sheet Z".
To get it the
Open the workbook, select Sheet Z and right-click on its name tab. Select
the [View Code] option from the list that appears.
Copy the code below and paste it into the module that appeared.
Modify any of the Const values that need to be modified. Close the VB Editor.

After that, each time you select Sheet Z, the list on it will be updated
from the list on Sheet X, so it will always be current based on the contents
of Sheet X.

There's also a line of code that is currently commented out that you can
turn into an active statement once you are sure things are working properly.
When you do that, it will update SheetZ much faster.

Here's the code, hope it helps:

Private Sub Worksheet_Activate()
'auto update from SheetX
'
'change these constants to
'match worksheet names and
'columns/rows used
'
Const sheetXName = "SheetX"
Const xClassColumn = "A"
Const xClassStudentsCol = "B"
Const xFirstClassRow = 2
'these have to do with
'this sheet (sheetZ)
Const zClassColumn = "A"
Const zFirstClassRow = 2
'end of user defined constants
Dim xSheet As Worksheet
Dim xSheetClassList As Range
Dim anyxSheetClass As Range
Dim offsetToStudentCount As Integer
Dim zRow As Long
Dim zBaseCell As Range

'
'once you are sure it is working,
'remove the apostrophe from the beginning of
'the next instruction to improve speed
'Application.ScreenUpdating = False

'start by erasing old entries on SheetZ
'and rebuilding the labels in row 1
Cells.ClearContents
Range("A1") = "Class"
Range("B1") = "No. Students"
Set xSheet = ThisWorkbook.Worksheets(sheetXName)
If xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Row _
< xFirstClassRow Then
'no classes on SheetX, do nothing
Set xSheet = Nothing
Exit Sub
End If
Set xSheetClassList = xSheet.Range(xClassColumn & _
xFirstClassRow & ":" & _
xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Address)
offsetToStudentCount = Range(xClassStudentsCol & 1).Column - _
Range(xClassColumn & 1).Column
Set zBaseCell = Range(zClassColumn & zFirstClassRow)
zRow = 0
For Each anyxSheetClass In xSheetClassList
If anyxSheetClass.Offset(0, _
offsetToStudentCount) 0 Then
zBaseCell.Offset(zRow, 0) = anyxSheetClass
zBaseCell.Offset(zRow, offsetToStudentCount) = _
anyxSheetClass.Offset(0, offsetToStudentCount)
zRow = zRow + 1
End If
Next
'housekeeping
Set xSheetClassList = Nothing
Set xSheet = Nothing
Set zBaseCell = Nothing
End Sub


"apache007" wrote:

Dear Excel experts

I have a workbook with Sheet X and Z.

On Sheet X, there are these coloums

Class Number of Students Male Female

AA 10 3 7
YY
ZZ 0
NN
OO 20 5 15

(More data at a later date)

How do I have Excel 2003 fill data on Sheet Z, so that it will Print out
those Classes that have number of students (ignore zeros and null)?

Result:

Class Number of Students
AA 10
OO 20

Please remember that Sheet Z will be added with more data as time goes by
and I want SHeet Z be able to keep spitting out Classes that have students.

Thank you for your advice.