I'm a little confused by the question including VB logic and formula - are
you looking for a macro to do it or for a worksheet function?
With worksheets you could simply link all data to all other sheets and
filter each of the sheets individually. Similarly you could NOT link to
anyplace else and just filter the 'master' list based on ages you wish to
examine.
For VB/macro logic, yes - you'd use the master list sheet's _Change() event
to detect a change in the Age column and based on the value, move the data
from that row to a specified sheet. The code below would do something
similar to that - all sheet names used are presumed to exist in the workbook,
and I'm assuming that your data begins with Name in column A, age in column B
and so on to Code in F. Rather than copying actual values, it sets up a link
formula because I can see you typing in a Name and Age and the data all gets
moved, but you've not typed in the other info yet! This way things are set
up to display the data when it finally does get typed in, or even if you edit
in the master list later.
To put the code in the proper place, go to your master list sheet and
right-click on the sheet's name tab and choose [View Code] from the list that
pops up. Copy this code and paste it into the code module that appears.
make any changes you need to to worksheet names, or even to the Select Case
... Case Is statements to include more than are already there.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim destSheetName As String
Dim sourceSheetName As String
Dim destNextRow As Long
Dim anyFormula As String
If Application.Intersect(Range("B:B"), Target) Is Nothing Then
Exit Sub ' change did not happen in column B
End If
sourceSheetName = ActiveSheet.Name
Select Case Target
'.Value is default so this works
Case Is = 11 ' set up for 11 year old's sheet
destSheetName = "11YrOlds"
Case Is = 12 ' set up for 12 year old's
destSheetName = "12YrOlds"
Case Is = 13 ' etc. etc.
destSheetName = "13YrOlds"
Case Is = 14
destSheetName = "14YrOlds"
Case Is = 15
destSheetName = "15YrOlds"
Case Is = 16
destSheetName = "16YrOlds"
Case Else
'all ages not specified above
destSheetName = "OddAges"
End Select
destNextRow = Worksheets(destSheetName).Range("B" _
& Rows.Count).End(xlUp).Row + 1
'since user may not be finished with complete
'row entry, we will set up linking formulas
'rather than transferring values
'link to name data
Worksheets(destSheetName).Range("A" & destNextRow).Formula = _
"='" & sourceSheetName & "'!" & "A" & Target.Row
'link to age
Worksheets(destSheetName).Range("B" & destNextRow).Formula = _
"='" & sourceSheetName & "'!" & "B" & Target.Row
'link to location
Worksheets(destSheetName).Range("C" & destNextRow).Formula = _
"='" & sourceSheetName & "'!" & "C" & Target.Row
'link to status
Worksheets(destSheetName).Range("D" & destNextRow).Formula = _
"='" & sourceSheetName & "'!" & "D" & Target.Row
'link to gender
Worksheets(destSheetName).Range("E" & destNextRow).Formula = _
"='" & sourceSheetName & "'!" & "E" & Target.Row
'link to code
Worksheets(destSheetName).Range("F" & destNextRow).Formula = _
"='" & sourceSheetName & "'!" & "F" & Target.Row
End Sub
Dave VB logic for excel said:
Hello,
Is there any VB formula to transfer data from Main to shee1,sheet2 and etc?
Im doing some program and i want to keep separate the information from main
sheet to the particular sheets. I can use link option but i need to filter
according to age.
I've attached the example.
Name Age Location Status Sex Code
I want the information stored in seperate sheet when enter the age. When
enter age 11, it should go to sheet 1. When enter age 12, it should go to
sheet 2 and the rest.
