Structuring if-then to copy/paste rows into separate sheet

[dani22]

Hi guys!

I am pretty good with excel but not code. I think my issue is going to need code as I dont think this is possible with if/then. The master excel sheet is constantly added to, so I cant use a manual filter/copy/paste approach. I cant add an image so I put the column names below.

What I need is something to search columns 'Agent1' 'Agent2' 'Agent3' for names. If the name of the agent comes up, then the row needs to be copy/pasted into a separate sheet under their name. I.e Agent1 can be Justin, Agent2 can be Ben, and Agent3 column blank. I will need the row to copy/paste into separate sheets one for Justin and one for Ben. Another example- Vikram as Agent1 and Agent2 and 3 blank. The row will need to copy/paste into Vikram's sheet.

The purpose of this is so I can have a master sheet but individual sheets for further calculation purposes. It is too big/complicating to all do on the same sheet. Any help will be greatly appreciated! Excel 2016 on a PC.


[TABLE="width: 1538"]
<colgroup><col width="395"><col width="56"><col width="54"><col width="56"><col width="88"><col width="113"><col width="84"><col width="95"><col width="118"><col width="84"><col width="118"><col width="76"><col width="118"><col width="83"></colgroup><tbody>[TR]
[TD="class: xl65, width: 395"]Client/Property Address
[/TD]
[TD="class: xl66, width: 56"]Buyer[/TD]
[TD="class: xl66, width: 54"]Seller[/TD]
[TD="class: xl66, width: 56"]Status[/TD]
[TD="class: xl66, width: 88"]close date[/TD]
[TD="class: xl66, width: 113"]Price[/TD]
[TD="class: xl66, width: 84"]%[/TD]
[TD="class: xl66, width: 95"]GCI total
[/TD]
[TD="class: xl66, width: 118"]Agent1
[/TD]
[TD="class: xl66, width: 84"]GCI Agent1[/TD]
[TD="class: xl66, width: 118"] Agent2
[/TD]
[TD="class: xl66, width: 76"]GCI Agent2[/TD]
[TD="class: xl66, width: 118"] Agent3
[/TD]
[TD="class: xl66, width: 83"]GCI Agent3[/TD]
[/TR]
</tbody>[/TABLE]

 

[L. Howard]

Questions...

Would you search ALL three Agent columns EACH time looking for a Ben or Vikram?

Or will you search only Agent1 column for a name, say Justin? Then if Justin is found or not, you may search Agent3 column for Vikram.

Are the names in the Agent columns and the names of their sheets to be copied to EXCATLY the same? And if NOT is it possible to 'normalize the names being searched and that persons sheet to be exactly the same?
Also with the names, is Ben actually Benjamin T. Wintworth III and is Justin something Mr. Justin Tyme

Are the three Agent(1,2,3) columns the only place in the 14 columns the names will appear?

Howard

 

[dani22]

Would you search ALL three Agent columns EACH time looking for a Ben or Vikram?
Yes, all three columns.

Or will you search only Agent1 column for a name, say Justin? Then if Justin is found or not, you may search Agent3 column for Vikram.
Agent1 etc represent the agents who are included in on a deal for pay. Agent1 is typically the primary agent, so all columns need to be searched for name results as they can appear on a different deal as Agent2 or Agent3

Are the names in the Agent columns and the names of their sheets to be copied to EXCATLY the same? And if NOT is it possible to 'normalize the names being searched and that persons sheet to be exactly the same?
Also with the names, is Ben actually Benjamin T. Wintworth III and is Justin something Mr. Justin Tyme
Yes- all names will be consistent throughout. i.e Ben would always appear as Ben - never as Benjamin, etc.

Are the three Agent(1,2,3) columns the only place in the 14 columns the names will appear?
Yes

I brainstormed and the best thing I could come up with is the master sheet which auto sorts into individual sheets. I will need to calculate units sold, commission cuts, YTD, etc for each individual. But I need the master sheet for overarching snap shots.

 

[L. Howard]

Try this in a standard module.

Enter the name to be searched for in cell P1 (I used a drop down in P1) then run the code.
The sheet names MUST be named exactly the same as the name in P1.
The master sheet in my workbook is named dani22.

Howard

Option Explicit

Sub Copy_Stuff_dani22()
Dim OneRng As Range, Hdr As Range
Dim c As Range
Dim Nme As String
Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In Worksheets

    Select Case ws.Name
      Case "dani22"
        ' do nothing
        
      Case Else
        ws.Cells.ClearContents
        
    End Select
Next ws
                       
Set OneRng = [I][B]Sheets("dani22").[/B][/I]Range("A2:N" & Cells(Rows.Count, "A").End(xlUp).Row)
Set Hdr = Sheets("dani22").Range("A1").Resize(1, 14)

Nme = Range("P1")


For Each c In OneRng
 
   If Not c Is Nothing And c = Nme Then
  
     c.EntireRow.Copy
     Sheets(Nme).Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
     Hdr.Copy Sheets(Nme).Range("A1")
     
   End If
   
Next
Application.ScreenUpdating = True

End Sub
 
'/ If other sheets exist and you DO NOT want to clear them
'/ use something like this in the Select Case
 'Case "dani", "Sheet1", "Sheet2", "Sheet_with_Funny_Name", "333 Counterfeit"
    ' do nothing

 

[L. Howard]

Actually the sheet clearing code seems to be a bad method after further testing.

Here is a revised code the only searches columns I, K & M, and does not clear the destination sheets.

I added a color index marking to show the names found by column (for my info only, can easily be removed).

Howard

Sub Copy_Stuff_dani22_Column()
Dim OnerngI As Range, OnerngK As Range, OnerngM As Range, Hdr As Range
Dim c As Range
Dim LRow As Long
Dim Nme As String
Dim ws As Worksheet
Application.ScreenUpdating = False
                       
LRow = Cells(Rows.Count, "I").End(xlUp).Row

Set OnerngI = Range("I2:I" & LRow)
Set OnerngK = Range("K2:K" & LRow)
Set OnerngM = Range("M2:M" & LRow)
Set Hdr = Sheets("dani22").Range("A1").Resize(1, 14)

Nme = Range("P1")

For Each c In OnerngI
 
   If Not c Is Nothing And c = Nme Then

  c.Interior.ColorIndex = 3
     c.EntireRow.Copy
     Sheets(Nme).Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
     Hdr.Copy Sheets(Nme).Range("A1")
     
   End If
   
Next

For Each c In OnerngK
 
   If Not c Is Nothing And c = Nme Then

  c.Interior.ColorIndex = 4
     c.EntireRow.Copy
     Sheets(Nme).Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
     Hdr.Copy Sheets(Nme).Range("A1")
     
   End If
   
Next

For Each c In OnerngM
 
   If Not c Is Nothing And c = Nme Then

 c.Interior.ColorIndex = 5
     c.EntireRow.Copy
     Sheets(Nme).Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
     Hdr.Copy Sheets(Nme).Range("A1")
     
   End If
   
Next

Application.ScreenUpdating = True

End Sub