VBA - Copy Row to New Sheet if Specific Name Appears in 1 of 10 Rows

[kterps]

Hello,


I'm working off an excel file used to track overall and individual performance scores on jobs (for a moving company). Right now, everything is entered/copied into the multiple sheets manually, which is unnecessary, but I do not know anything about macros.


The Master sheet will look something like this:


[TABLE="width: 500"]
<tbody>[TR]
[TD]11
[/TD]
[TD]Pack Date[/TD]
[TD]Cust. Name[/TD]
[TD]Deliver Date[/TD]
[TD]Score 1[/TD]
[TD]S.2[/TD]
[TD]S.3[/TD]
[TD]S.4[/TD]
[TD]S.5[/TD]
[TD]Worker 1[/TD]
[TD]W.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Worker 10[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]1.1.16[/TD]
[TD]Smith[/TD]
[TD]1.2.16[/TD]
[TD]100[/TD]
[TD]75[/TD]
[TD]75[/TD]
[TD]100[/TD]
[TD]50[/TD]
[TD]Bob[/TD]
[TD]Rob[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Where a row contains job info, 5 different scores, and individual columns for each person working on that job (which is never more than about 5 or 6? I made 10 just to be safe). It also gives me 10 rows to run some simple functions to get some stats once I get this running. Individual sheets will look the same as the Master sheet.


I'd like to build a macro that copies an entire row from the master sheet and pastes it into an individual's sheet if that individual's name appears in any of the worker columns. It would be best if the macro could be set up so individual sheets automatically update when new jobs are entered into the Master sheet. Thank you all in advance!

 

[My Aswer Is This]

Try this for now:
This script will allow you to double click on a workers name and that row of data will be copied to a sheet named exactly as the name double clicked on.

For example double click on "Dad" and this row of data will be copied to a sheet named "Dad"

This is not exactly what you asked for but try this and see how it works.
Copying the row to all the workers sheet in that row may be more difficult not knowing what columns have the worker names and sometimes there may be one or as many as ten columns with worker names.

Please give details as to what columns may have worker names. Explain by saying column E to column H for example. But for now try the below script.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the "Master" Sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Now when you double click on any cell that rows data will be copied to that sheet.
If you double click on "Bob" that rows data will be copied to a sheet named "Bob"
Be sure and double click on exact names. Like "Bob" goes to sheet "Bob" do not double click on "bob"
And expect this to be copied to "Bob"

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
On Error GoTo M
Dim Lastrow As Long
Lastrow = Sheets(Target.Value).Cells(Rows.Count, "A").End(xlUp).Row + 1
Rows(Target.Row).Copy Destination:=Sheets(Target.Value).Rows(Lastrow)
Exit Sub
M:
MsgBox "No such sheet exist"
End Sub

 

[kterps]

I am so sorry for the delay in my response!! I must not have notifications from my email set up? In any case, thank you for your response!

To answer your question, worker names will only appear in columns J to S, everything before column J contains the job info. The 10 columns saved for worker names is simply a big fluffy cushion, I've never seen more than 5 people on a job but want to be able to handle it if some weird situation comes up.

The code you provided works great! It's pretty much exactly what I was after, just with the extra step of double clicking the individual's name.

I really appreciate your assistance!!

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

I am so sorry for the delay in my response!! I must not have notifications from my email set up? In any case, thank you for your response!

To answer your question, worker names will only appear in columns J to S, everything before column J contains the job info. The 10 columns saved for worker names is simply a big fluffy cushion, I've never seen more than 5 people on a job but want to be able to handle it if some weird situation comes up.

The code you provided works great! It's pretty much exactly what I was after, just with the extra step of double clicking the individual's name.

I really appreciate your assistance!!

 

[kterps]

Well, I'd really hate to be a bother, but is it possible to achieve this same function without the double click?

 

[My Aswer Is This]

I will have to think about it more. The difficult part is worker names may be in as many as 10 columns.

Worker names will be in columns(8) to (18) is that correct?

 

[kterps]

That is totally fine, this is already so much further than I would have gotten on my own!

In my current draft of the file, worker names would be in columns(10) to (19).

I would most certainly be open to an alternative method for setting up the spreadsheet to better accommodate this macro, so long as no information is lost. For example (even if this isn't accurate), if workers on a job were listed in a single column so that the macro only needed to look at one "target", so to speak; here, all the workers on a job are still recorded (even if messy to look at) but in a way easier for the macro to run.

 

[My Aswer Is This]

OK. try this script

It assumes worker names will be in column 10 to 19 not all columns must have names but it will look no further then column 19 make sure you have nothing in these columns other then sheet names. If you enter a value that is not a sheet name you will get an error.

To activate the script you must double click on column "A"
If you do not want to double click on column ''A" then I need to know what action should activate the script. But do not say when I enter a workers name because then the script will not know when you have finished entering worker names.
A double click on one column should not be that difficult. I have also told the script to turn that cells interior color to green when you double click on column "A" this will let you be assured this rows data has been copied over.
In you want you can remove that line of code marked in red.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Cancel = True
On Error GoTo M
Dim Lastrow As Long
    For Each c In Range(Cells(Target.Row, 10), Cells(Target.Row, 19))
        If c.Value <> "" Then
            Lastrow = Sheets(c.Value).Cells(Rows.Count, "A").End(xlUp).Row + 1
            Rows(Target.Row).Copy Destination:=Sheets(c.Value).Rows(Lastrow)
            
        End If
    Next
[COLOR=#ff0000]Target.Interior.ColorIndex = 4[/COLOR]
Exit Sub
End If
M:
MsgBox "No such sheet exist"
End Sub

 

[kterps]

My Answer Is This,

This script is absolutely perfect!! It's very smooth and simple to use! I really like the cell formatting element you included too, a great visual check and balance that also shows up in the other sheets (in the event of "double entry"). It should both reduce entry issues and make solving them super easy!

Put simply, there is nothing else I could ask from this macro! Thank you SO much for the time you've spent to help me out, I am very much grateful!!

 

[My Aswer Is This]

Glad I was able to help you and I'm learning more every day myself.

You will see in the below line of code it allows you to put Worker names in column 10 to 19
If you later on want to add more then just change 19 to 21 or what ever you need.

For Each c In Range(Cells(Target.Row, 10), Cells(Target.Row, 19))

Take care and come back to Mr. Excel next time you need more assistance