Auto-populate info. from master sheet to multiple sheets [Solved/Closed]

Posts
11
Registration date
Wednesday June 21, 2017
Last seen
July 19, 2017
-
Hi,

I have been working on an excel sheet for the past month and have not found a solution that would do what I need it to do. I'm sure there have been lots of similar questions and I've tried many different ways, but I'm stuck now. So my main goal is to auto-populate my information on my master sheet into multiple sheets based on a sponsor's name. My data are in columns A - Q (project id, project name, sponsor name, etc.) and is put into a table since I need to be able to filter. Is there a way for me to auto-populate my data? Also is there a way to do it where there is a table in the sponsor's sheet as well? I will be adding columns to the end of the sponsor's sheet as well.

Example:
Master sheet has the following listed in a table:
(A B C D..... to Q)

1. Project ID Project Name Bob B. ... etc.
2. Project ID Project Name Billy J. ... etc.
3. Project ID Project Name Annie F. ...etc.
4. Project ID Project Name Bob B. ....etc.

I would like it to be like this in the sponsor's sheet, but in a table as well:

1. Project ID Project Name Bob B.
2. Project ID Project Name Bob B.

Please let me know if you have any questions. Any help would be greatly appreciated!
See more 

5 replies

Best answer
Posts
2435
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 4, 2018
1
Thank you
Hi Kvil,

I think you are in need of the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("Q")) Is Nothing Then Exit Sub
Target.EntireRow.Copy Sheets(Target.Offset(0, -14).Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub


To implement the code, right-click the master sheets tab and select View Code. Paste the code in the big white field that appears.

What does the code do? Whenever you confirm an entry in column Q of your master sheet, the entire row is copied and pasted to the first available row of the sheet (the sheet needs to exist) specified in column C of your master sheet.

Is that what you were after?

Best regards,
Trowa

Thank you, TrowaD 1

A few words of thanks would be greatly appreciated. Add comment

CCM has helped 2601 users this month

kvil
Posts
11
Registration date
Wednesday June 21, 2017
Last seen
July 19, 2017
-
Hi Trowa,

I pasted the code and did what you said, but it's not giving me anything. I have also saved my file as .xlsm. Any idea on what I might be doing wrong?
kvil
Posts
11
Registration date
Wednesday June 21, 2017
Last seen
July 19, 2017
-
Hi Trowa,

Please disregard my previous reply. I was able to get it to work. However, when I went to update one of my cells in a row that had previously been entered, it added a duplicate row in the sponsor sheet rather than just overwriting it with the correct changes. Is there a way to do that instead of creating duplicate rows of the same project in the sponsor's sheet?
Posts
2435
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 4, 2018
1
Thank you
Hi Kvil,

To do that, there needs to be a unique value in the row to locate it's location in the destination sheet.

I assumed that the project ID's in column A are unique values. If so, then use the code below.

When you want to use a different column, then have a look at code line 6 & 7. Here you will find the following part:
.Columns("A").Find(Target.Offset(0, -16).Value)
Change the A and also the -16 which is the relative position of column A from column Q.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim mFind As Range

If Intersect(Target, Columns("Q")) Is Nothing Then Exit Sub

Set mFind = Sheets(Target.Offset(0, -14).Value).Columns("A").Find(Target.Offset(0, -16).Value)

If mFind Is Nothing Then
    Target.EntireRow.Copy Sheets(Target.Offset(0, -14).Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Else
    Target.EntireRow.Copy mFind
End If
End Sub


Best regards,
Trowa

Thank you, TrowaD 1

A few words of thanks would be greatly appreciated. Add comment

CCM has helped 2601 users this month

kvil
Posts
11
Registration date
Wednesday June 21, 2017
Last seen
July 19, 2017
-
Hi Trowa,

This works wonderfully. Thank you so much for your time and help, I truly appreciate it.

Thanks,

Kvil
TrowaD
Posts
2435
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 4, 2018
-
Awesome!

Thanks for the feedback!
kvil
Posts
11
Registration date
Wednesday June 21, 2017
Last seen
July 19, 2017
> TrowaD
Posts
2435
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 4, 2018
-
Trowa,

I ran into a little problem. I was wondering if there was a way to not get an error code when I delete rows. It still works great after I delete a row and I click "End", but it keeps giving me the following error message "Run-time error '1004': Application-defined or object-defined error" Do you think this is something that can be updated or any idea?

Thank you,

kvil
Posts
2435
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 4, 2018
1
Thank you
Hi Kvil,

Paste the following code snippet between code lines 4 & 6:
If Target.Cells.Count > 1 Then Exit Sub


That should fix it.

Best regards,
Trowa

Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.

Thank you, TrowaD 1

A few words of thanks would be greatly appreciated. Add comment

CCM has helped 2601 users this month

kvil
Posts
11
Registration date
Wednesday June 21, 2017
Last seen
July 19, 2017
-
Trowa,

It works! You're amazing! Thank you so much for your help.

Best Regards,

Kvil
kvil
Posts
11
Registration date
Wednesday June 21, 2017
Last seen
July 19, 2017
-
Hi Trowa,

Sorry to ask again, but is there a way to just get the rows from columns A-Q? I tried this, but now it's just overwriting the first row of the sponsor's worksheet

Target.EntireRow.Copy Sheets(Target.Offset(0,0).Value).Range("A:Q").End(xlUp).Offset(1, 0)


Thank you for your help on all of this.

-Kvil
kvil
Posts
11
Registration date
Wednesday June 21, 2017
Last seen
July 19, 2017
-
I think I was able to get it to work properly. Is this how you would have done it? I'm not sure if there might be some problems to it in the future or not, but it seems to be working properly so far.

If mFind Is Nothing Then
    Target.EntireRow.Range("A1:P1").Copy Sheets(Target.Offset(0, 0).Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Else
    Target.EntireRow.Range("A1:P1").Copy mFind
End If


Thank you,

kvil
Posts
2435
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 4, 2018
1
Thank you
Hi Kvil,

I'm actually surprised that works!

I would have used this:
Range(Cells(Target.Row, "A"), Cells(Target.Row, "P"))
instead of this:
Target.EntireRow.Range("A1:P1")

I also noticed you used this:
.Offset(0, 0)
Which doesn't do anything and can be removed.

Hope this helps.

Best regards,
Trowa

Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.

Thank you, TrowaD 1

A few words of thanks would be greatly appreciated. Add comment

CCM has helped 2601 users this month

kvil
Posts
11
Registration date
Wednesday June 21, 2017
Last seen
July 19, 2017
-
Hi Trowa,

Thank you for everything!

Best,

Kvil
0
Thank you
hi!

I have the same problem but it doesn't work. Help please!
TrowaD
Posts
2435
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 4, 2018
-
Hi Hpp,

Could you explain your problem and what doesn't work?

Best regards,
Trowa