Transfer Data from a sheet to another many sheets

Closed
polybag3 Posts 3 Registration date Sunday November 30, 2014 Status Member Last seen December 15, 2014 - Nov 30, 2014 at 11:19 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Dec 22, 2014 at 11:45 AM
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.

9 replies

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Dec 4, 2014 at 10:56 AM
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.
0
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!
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Dec 8, 2014 at 11:09 AM
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
0
polybag3 Posts 3 Registration date Sunday November 30, 2014 Status Member Last seen December 15, 2014
Dec 13, 2014 at 04:54 PM
Hi TrowaD
Here i'm again..
I would like to help me again.
i have some questions on the code:
- First, i would like if the cell named "INCOME" is blank , not transferred the data. Similar and if the cell named"EXPENSES" is blank.
- Also I would like if i need to correct the cells in sheet named "DATA" to automatically corrected to other sheets.
- The same and if i need to delete a row in sheet "DATA" to automatically deleted to other sheets.
- Finally I would like a help on how to sort the data in sheet "DATA" by date and transferred in other sheets sorted.

Sorry for the many questions and thanks for your time!
Thanks in advanced
0
Perfect!!!!!!
Thank you so much for your help and your time!
I really appreciate it!
0

Didn't find the answer you are looking for?

Ask a question
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Dec 15, 2014 at 11:30 AM
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
0
polybag3 Posts 3 Registration date Sunday November 30, 2014 Status Member Last seen December 15, 2014
Dec 15, 2014 at 12:44 PM
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:=xlTopToBottom

End 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!!!!
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Dec 16, 2014 at 12:06 PM
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.
0
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!
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Dec 22, 2014 at 11:45 AM
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


0