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

Question

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!

TrowaD · Answer

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

TrowaD · Answer

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

TrowaD · Answer

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.

TrowaD · Answer

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.

hpp · Answer

hi!

I have the same problem but it doesn't work. Help please!

Auto-populate info. from master sheet to multiple sheets

5 responses

Similar discussions

Subscribe To Our Newsletter!