Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VB formula for Copy
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VB formula for Copy
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" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VB formula for Copy
Thank you very much.... its works!!!!!!!!!!!
"JLatham" wrote: 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" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VB formula for Copy
Hello,
Im not creating separate sheet for each age. I plan to do all in 1 sheet by creating a table for each age. Ive drawn the table to make easier to understand. 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. Is there any VB formula that I can use to do this? Thank you "JLatham" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I copy a formula and the results copy from the original cell | Excel Discussion (Misc queries) | |||
copy formula down a column and have cell references change within formula | New Users to Excel | |||
want to copy formula, only change one number in formula | Excel Worksheet Functions | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
Copy formula so destination displays formula as text | Excel Discussion (Misc queries) |