Auto-populate information from one sheet to another

mi22lo

New Member
Joined
Dec 18, 2020
Messages
3
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. MacOS
Dilemma: I would like to auto-populate the information from sheet 1 to other sheets depending on the information provided.
Example: Sheet 1 is the Main Matrix it has 6 columns.
Col 1: Name of a person
Cols 2-6: Room Assignments
---- Col 2: Pantry; Col 3: Front Desk; Col 4: Board Room; Col 5: Security; Col 6: VIP Room
If I mark "x" on Col 3 for person 1, the person's name should be automatically copied to the sheet "Front Desk". If I mark "x" on Col 2 and Col 4 for person 2, the person's name should be automatically copied to the sheet "Pantry" and "Board Room".
I am not that familiar with macros. Hope someone can help me. Attached is the sample look.
 

Attachments

  • Screen Shot 2020-12-18 at 2.28.45 PM.png
    Screen Shot 2020-12-18 at 2.28.45 PM.png
    32.1 KB · Views: 19

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Where do you want the names entered on the destination sheets? Which Column, which row?
 
Upvote 0
Here is an event procedure that will fire when you make changes to the Master Matrix sheet. It will only execute if the change is entry of an X in a cell in columns B:F.
The code should be copied and pasted into the sheet code module of the Master Matrix sheet. The code assumes the destination cell is the next available cell in column A.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim shNm As String
If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("B:F")) Is Nothing Then
        shNm = Cells(1, Target.Column).Value
        If Application.CountIf(Sheets(shNm).Columns("A"), Cells(Target.Row, 1).Value) = 0 Then
            If UCase(Target.Value) = "X" Then
                Cells(Target.Row, 1).Copy Sheets(shNm).Cells(Rows.Count, 1).End(xlUp)(2)
            End If
        End If
    End If
End Sub

It was also assumed that the column headers for columns B:F are exactly the same as the destination sheet name tab names. If not, the code will fail.
 
Upvote 0
It depends on where the "X" is marked.
Example:
1. On Main Matrix Sheet, if I mark "X" on C2 for person 1, the person 1's name should be automatically copied to the sheet "Front Desk" or A2 of sheet "Front Desk".
2. On Main Matrix, if I mark "X" on C3 and D3 for person 2, the person's name should be automatically copied to the sheet "Front Desk" or A3 of sheet "Front Desk" and sheet "Board Room" or A2 of sheet "Board Room".
 
Upvote 0
Here is an event procedure that will fire when you make changes to the Master Matrix sheet. It will only execute if the change is entry of an X in a cell in columns B:F.
The code should be copied and pasted into the sheet code module of the Master Matrix sheet. The code assumes the destination cell is the next available cell in column A.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim shNm As String
If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("B:F")) Is Nothing Then
        shNm = Cells(1, Target.Column).Value
        If Application.CountIf(Sheets(shNm).Columns("A"), Cells(Target.Row, 1).Value) = 0 Then
            If UCase(Target.Value) = "X" Then
                Cells(Target.Row, 1).Copy Sheets(shNm).Cells(Rows.Count, 1).End(xlUp)(2)
            End If
        End If
    End If
End Sub

It was also assumed that the column headers for columns B:F are exactly the same as the destination sheet name tab names. If not, the code will fail.
What if the column headers are different? Is there an alternative code for it?
 
Upvote 0
Something on the source sheet (Master Matrix) has to identify which sheet the string in column A is to be copied to. The current code assumes that is the headers in row 1 of columns B:F.
If you prefer a different range of cells to list the sheet names then the code would need to be changed to use those cells to find the appropriate sheets. But the simple fact is, that vba is not a magical tool that can just take data from one sheet and put it where a user wants it on another sheet without any other guidance. The code operates on logic and simply does what you would do if you were doing it manually, only much faster. The difference is, the computer (at least vba) cannot use intuitive reason like a human to detect misspelled words and abbreviations that mean the same thing as data in another location. With vba when attempting to match data the general rule is that for sheet names listed on one sheet, they have to be the same on the other sheet or they won't match. (There are exceptions, but not in your particular case for this code). If you want the code to work, make sure your headers on Master Matrix match the names on the five sheet name tabs. That is how vba finds the correct sheet.

The code should put the names where you want them in column A. It has a built in edit to prevent entering duplicates.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top