Perhaps you consider using the logic in your name and use the suggestions of
autofilter.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dave VB logic for excel"
wrote in message ...
JLatham...... thank you very much. You just gave exactly i was looking
for.
Thanks alot....

"JLatham" wrote:

Even though I think using Auto Filter is the safest, least error prone
way to
do things...

But if you INSIST! Here's worksheet code that will do a rudimentary job
of
what you want. Set up your table sheet initially in the following
fashion.
Note that the
age values in the "For Age ##" entries should be in column B, not as part
of
"For Age" in column A.

A B C D E
1 For Age 11
2 Name Age Sex Status Code
3
4 For Age 12
5 Name Age Sex Status Code
6
7 For Age 13
8 Name Age Sex Status Code
9
continue, with blank row after each table's existing entries or label row
(2, 5, 8 above: i.e. the row above "For Age" entries must be empty.

To put the code in the proper place, right-click on the master list
sheet's
name-tab and choose [View Code] from the list. Then copy and paste the
code
below into that
module - make any changes to sheet names and column IDs that are required
by
your actual setup.

Private Sub Worksheet_Change(ByVal Target As Range)
'creates link formulas within the destSheet tables
'back to the entry row on the sourceSheet when
'the age is entered on the sourceSheet
'POTENTIAL PITFALLS:
' you type in an incorrect age and go back and correct it
' you end up with an entry for that individual in 2 tables!
' although age will show as incorrect in one of the tables.
' you type in what you think is correct age, then think that
' is wrong, change it, then realize the first entry was correct
' you end up with 2 entries in correct table
' and 1 in left in the wrong table (with wrong age showing).
' you delete a row on the sourceSheet with data in it
' you end up with a #REF! error in your table
' Probably some more I haven't even imagined!

'change the constants as required by your reality
'the minimum age to work with (have tables for)
Const minAge = 10
'the maximum age to work with (have tables for)
Const maxAge = 15
'name of sheet with master list
Const sourceSheet = "Sheet1"
'these are set up individually so that
'your table on source/dest sheets can
'occupy different columns
Const srcNameCol = "A"
Const srcAgeCol = "B"
Const srcSexCol = "C"
Const srcStatusCol = "D"
Const srcCodecol = "E"

'name of sheet with tables on it
Const destSheet = "Sheet2"
'these are set up individually so that
'your table on source/dest sheets can
'occupy different columns
Const destNameCol = "A"
Const destAgeCol = "B"
Const destSexCol = "C"
Const destStatusCol = "D"
Const destCodecol = "E"

Dim anyText As String
Dim destWS As Worksheet
Dim ageRange As Range
Dim lastRow As Long
Dim anyCell As Range

anyText = srcAgeCol & ":" & srcAgeCol
'did change take place in proper column
If Application.Intersect(Target, Range(anyText)) _
Is Nothing Then
Exit Sub
End If
'test if multiple cells selected or
'cell changed is empty via [Del] or
'if the cell value in is non-numeric.
If Target.Cells.Count 1 Or _
IsEmpty(Target) Or _
Not (IsNumeric(Target)) Then
Exit Sub
End If
'test if is valid age
If Target < minAge Or Target maxAge Then
Exit Sub
End If
Set destWS = Worksheets(destSheet)
'use Rows.CountLarge for Excel 2007
lastRow = destWS.Range(destAgeCol & Rows.Count).End(xlUp).Row
Set ageRange = destWS.Range(destAgeCol & "1:" & _
destAgeCol & lastRow)
'find matching age entry group
'reset lastRow
lastRow = 0
For Each anyCell In ageRange
If anyCell.Value = Target.Value Then
'we have start of table, find end of it
lastRow = anyCell.End(xlDown).Offset(1, 0).Row
Exit For
End If
Next
'did we find a match?
If lastRow = 0 Then
Exit Sub ' no, didn't find matching age table
End If
'yes, found what we needed, insert new row for new data
destWS.Range(destAgeCol & lastRow).EntireRow.Insert
'enter data as link formulas so that any
'updates/changes are reflected in the table(s)
'and to cover the case where age gets entered
'before all other data has been entered.
destWS.Range(destNameCol & lastRow).Formula = _
"='" & sourceSheet & "'!" & srcNameCol & Target.Row
destWS.Range(destAgeCol & lastRow).Formula = _
"='" & sourceSheet & "'!" & srcAgeCol & Target.Row
destWS.Range(destSexCol & lastRow).Formula = _
"='" & sourceSheet & "'!" & srcSexCol & Target.Row
destWS.Range(destStatusCol & lastRow).Formula = _
"='" & sourceSheet & "'!" & srcStatusCol & Target.Row
destWS.Range(destCodecol & lastRow).Formula = _
"='" & sourceSheet & "'!" & srcCodecol & Target.Row
End Sub


"JLatham" wrote:

Didn't we cover this issue in this thread?
http://www.microsoft.com/office/comm...9-dadcf4066a21

Although in that one I provided code to move them to different sheets
based
on the input rather than into tables on just one 'second' sheet.
You'll also
note that in my second paragraph in that discussion I also recommended
considering just using Auto Filtering of your main list, just as Don
and Dave
have done in this discussion.

"Dave VB logic for excel" wrote:

Hello,

Im creating main spreadsheet and age spreadsheet.

Main Spreadsheet
Name Age Location Status Sex Code



Age Spreadsheet
For age 11
Name Age Location Status Sex Code



For age 12
Name Age Location Status Sex Code



For age 13
Name Age Location Status Sex Code



For age 14
Name Age Location Status Sex Code



So when I enter details in main spreadsheet, it should come under the
appropriate table in age spreadsheet. Is there any VB formula that I
can use
to do this?

Thank you