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

Report
Posts
11
Registration date
Wednesday June 21, 2017
Status
Member
Last seen
July 19, 2017
-
Posts
2657
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 22, 2020
-
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!
Related:

5 replies

Posts
2657
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 22, 2020
440
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
1
Thank you

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

CCM 2942 users have said thank you to us this month

Posts
11
Registration date
Wednesday June 21, 2017
Status
Member
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?
Posts
11
Registration date
Wednesday June 21, 2017
Status
Member
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
2657
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 22, 2020
440
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
1
Thank you

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

CCM 2942 users have said thank you to us this month

Posts
11
Registration date
Wednesday June 21, 2017
Status
Member
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
Posts
2657
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 22, 2020
440
Awesome!

Thanks for the feedback!
Posts
11
Registration date
Wednesday June 21, 2017
Status
Member
Last seen
July 19, 2017
>
Posts
2657
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 22, 2020

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
2657
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 22, 2020
440
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.
1
Thank you

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

CCM 2942 users have said thank you to us this month

Posts
11
Registration date
Wednesday June 21, 2017
Status
Member
Last seen
July 19, 2017

Trowa,

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

Best Regards,

Kvil
Posts
11
Registration date
Wednesday June 21, 2017
Status
Member
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
Posts
11
Registration date
Wednesday June 21, 2017
Status
Member
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
2657
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 22, 2020
440
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.
1
Thank you

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

CCM 2942 users have said thank you to us this month

Posts
11
Registration date
Wednesday June 21, 2017
Status
Member
Last seen
July 19, 2017

Hi Trowa,

Thank you for everything!

Best,

Kvil
hi!

I have the same problem but it doesn't work. Help please!
Posts
2657
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 22, 2020
440
Hi Hpp,

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

Best regards,
Trowa