Transfer Data from a sheet to another many sheets Closed

Question

Hello, 


I  am  trying  to  transfer  data  from  one  sheet  to  many  other  sheets .  For  example , when  I  write  data  to  a  row , I  would  like  the  same  data  tranferred  to  the  according  sheet . 
There are some examples in the files.

TrowaD · Answer

Hi Polybag3,

When do you want this to happen?

Manual:
Do you want to process the last row, manually select a row or process the whole sheet? 

Automatic:
Do you want to process the row you last entered a value in (which column do you enter lastly?)?

Do you have something else in mind?

Best regards,
Trowa
 
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.

polybag3 · Answer

Hi Trowa,
Thank you very much for your time!

I would like when i write George in a cell in a sheet named "DATA" , to transfer the whole row ( the  row  that  includes  George ) automatically to the  sheet named "GEORGE". 
Likewise , when i write John i would like to transfer the whole row ( the  row  that  includes  John) automatically to the  sheet named "John". 
etc...

Thanks in advanced!

TrowaD · Answer

Hi Polybag,

Here is your code (right click on the DATA tab > View code > then paste the code):
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("B:B")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub

Range(Cells(Target.Row, "A"), Cells(Target.Row, "C")).Copy _
Sheets(Target.Value & " - INCOME").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

Union(Range(Cells(Target.Row, "A"), Cells(Target.Row, "B")), Range("D" & Target.Row)).Copy _
Sheets(Target.Value & " - EXPENSES").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

Target.Select
End Sub

May I advise you to use a data validation list (found under the DATA tab of Excel ribbon) to make it easier and faster to input names and also prevents typo's.

Best regards,
Trowa

polybag3 · Answer

Perfect!!!!!!
Thank you so much for your help and your time! 
I really appreciate it!

TrowaD · Answer

Hi Polybag, 1. Done, check the code below. 2. This is tricky. Imagine Excel is checking A2 and B2 and comparing them to column A and B in another sheet, then Excel can't find a match if you happen to change the date. Consider the option to add an extra column with a reference number of some sort to identify matching data. 3. The way to handle this changes on what you decide to do with point 2. 4. Try this yourself: Record a macro sorting the data how you want, then use that code on each sheet you want to sort the same way you use the code below. Here is the code: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Columns("B:B")) Is Nothing Then Exit Sub If Target.Cells.Count > 1 Then Exit Sub If Target.Value = vbNullString Then Exit Sub If Target.Offset(0, 1) <> vbNullString Then Range(Cells(Target.Row, "A"), Cells(Target.Row, "C")).Copy _ Sheets(Target.Value & " - INCOME").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) End If If Target.Offset(0, 2) <> vbNullString Then Union(Range(Cells(Target.Row, "A"), Cells(Target.Row, "B")), Range("D" & Target.Row)).Copy _ Sheets(Target.Value & " - EXPENSES").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) End If Target.Select End Sub Let me know how you want to handle point 2 and 3 and if you can make point 4 work (if not, then let me know on which column you want to sort or in which order you want to sort the columns). Best regards, Trowa

polybag3 · Answer

Hi Trowa,
First of all , thank you for your time!!!!!

1. Its perfect!!
4. Im ok with all sheets except sheet data..
This is the code i have..
 Private Sub Worksheet_Change(ByVal Target As Range)    Range("A2").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottomEnd Sub 

Where can i paste this code to sheet "DATA"??

2,3 Really i dont have any idea.
Any proposa l- help - idea  accepted!!!!

Thank you again!!!!

TrowaD · Answer

Hi Polybag,

1. Great.

4. Place it before the last line "End Sub".
Worksheet_Change might not be the most suitable one, take a look and try some others. Here is a picture to guide you:


2, 3. My initial idea failed me, back to the drawing board. So please be patient, I won't forget you.

Best regards,
Trowa
 
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.

polybag3 · Answer

Hi Trowa,
 
Thank you so much..
I have a last question about the sort..
I would like to sort all columns except column G
With this code makes sort to all columns.
Any idea ??

Private Sub Worksheet_Change(ByVal Target As Range)
    Range("A2").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Thanks in advanced!

TrowaD · Answer

Hi Polybag,

Let me first answer your last question.
You can't sort column A to Z except G. So I hope column G is your last column, otherwise make it either your first or your last.

That being said:
Private Sub Worksheet_Change(ByVal Target As Range)
Range("A:F").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub 


Now I know there are more then 4 columns, which may ruin the solution I now have in my head.
I was thinking of skipping the idea of updating data.
When you want to change data in a row, delete it first, then enter it again.
To delete a row, I was thinking about a button, which once clicked, will ask you which row to delete.
This will eliminate the need for an extra column. 

Not sure how you feel about this, because of the additional columns you use, as well as the extra effort needed to make an alteration (do you just alter input mistakes or do you alter more frequently).

Could you upload your file with a few rows of data, so I can see if there is more I should take into account.
Upload using a filesharing site like www.speedyshare.com or ge.tt and then post back the download link. Always be careful with sensitive info.

Best regards,
Trowa

Transfer Data from a sheet to another many sheets

9 responses

Similar discussions

Subscribe To Our Newsletter!