Report

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

Ask a question kvil 11Posts Wednesday June 21, 2017Registration date July 19, 2017 Last seen - Last answered on Sep 12, 2017 at 10:41 AM by TrowaD
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!
Helpful
+1
plus moins
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
Was this answer helpful?  
kvil 11Posts Wednesday June 21, 2017Registration date July 19, 2017 Last seen - Jun 22, 2017 at 08:45 PM
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?
Reply
kvil 11Posts Wednesday June 21, 2017Registration date July 19, 2017 Last seen - Jun 24, 2017 at 05:19 PM
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?
Reply
Leave a comment
Helpful
+1
plus moins
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
Was this answer helpful?  
kvil 11Posts Wednesday June 21, 2017Registration date July 19, 2017 Last seen - Jun 26, 2017 at 11:47 AM
Hi Trowa,

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

Thanks,

Kvil
Reply
TrowaD 2281Posts Sunday September 12, 2010Registration date ModeratorStatus October 17, 2017 Last seen - Jun 26, 2017 at 12:00 PM
Awesome!

Thanks for the feedback!
Reply
kvil 11Posts Wednesday June 21, 2017Registration date July 19, 2017 Last seen - Jun 27, 2017 at 11:26 AM
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
Reply
Leave a comment
Helpful
+1
plus moins
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

Was this answer helpful?  
kvil 11Posts Wednesday June 21, 2017Registration date July 19, 2017 Last seen - Jun 27, 2017 at 12:17 PM
Trowa,

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

Best Regards,

Kvil
Reply
kvil 11Posts Wednesday June 21, 2017Registration date July 19, 2017 Last seen - Jun 27, 2017 at 02:02 PM
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
Reply
kvil 11Posts Wednesday June 21, 2017Registration date July 19, 2017 Last seen - Jun 28, 2017 at 01:07 PM
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
Reply
Leave a comment
Helpful
+1
plus moins
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

Was this answer helpful?  
kvil 11Posts Wednesday June 21, 2017Registration date July 19, 2017 Last seen - Jul 6, 2017 at 03:24 PM
Hi Trowa,

Thank you for everything!

Best,

Kvil
Reply
Leave a comment
Helpful
+0
plus moins
hi!

I have the same problem but it doesn't work. Help please!
TrowaD 2281Posts Sunday September 12, 2010Registration date ModeratorStatus October 17, 2017 Last seen - Sep 12, 2017 at 10:41 AM
Hi Hpp,

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

Best regards,
Trowa
Reply
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!