Selected Answer
Please try this code. Install it in a standard code module in your workbook (by default "Module1").
Option Explicit
Private Function ResultColumn(Master As Worksheet, _
RowColumn As Long) As Long
' 28 Sep 2017
' it is presumed that the Results column is the
' last column on the right of the "Master" sheet,
' and has a column caption in Row 1
' If you choose another column, this code must be replaced
' as well as the offset from it by which the RowColumn is identified.
Dim Fun As Long
With Master
Fun = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
ResultColumn = Fun
RowColumn = Fun + 3 ' 3 columns right of the Results
End Function
Sub CreateAgentCopies()
' 28 Sep 2017
Dim Ws As Worksheet
Dim Rng As Range
Dim Cell As Range, Tmp As String
Dim Spike As String
Dim Sp() As String, i As Long
Dim Rl As Long
Dim RowColumn As Long
' RowColumn will be used to match row numbers in the Master
' and the Agents' sheets
Application.EnableEvents = False
' give a name to this sheet. If you change the name
' use Edit/Replace to replace all instances in this project
With Worksheets("Master")
Rl = .Cells(.Rows.Count, "A").End(xlUp).Row
' Column A has the agents' names, ignore row 1 (= caption)
Set Rng = .Range(.Cells(2, "A"), .Cells(Rl, "A"))
ResultColumn Rng.Parent, RowColumn ' sets RowColumn
For Each Cell In Rng
Tmp = Cell.Value
If InStr(1, Spike, Tmp, vbTextCompare) = 0 Then
If Len(Spike) Then Tmp = "|" & Tmp
Spike = Spike & Tmp
End If
Cells(Cell.Row, RowColumn).Value = Cell.Row
Next Cell
' extend the range to include the added column
Set Rng = .Range(.Cells(1, "A"), .Cells(Rl, RowColumn))
Sp = Split(Spike, "|")
For i = 0 To UBound(Sp)
On Error Resume Next
Set Ws = Worksheets(Trim(Sp(i)))
If Err Then
Set Ws = Worksheets.Add(After:=Sheets(Worksheets.Count))
Ws.Name = Trim(Sp(i))
Else
Ws.UsedRange.ClearContents
End If
Rng.AutoFilter Field:=1, Criteria1:=Sp(i)
.AutoFilter.Range.Copy
With Ws.Cells(1, 1)
.PasteSpecial Paste:=8 ' copy the column widths
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
Ws.Columns(RowColumn).Hidden = True
Next i
.AutoFilterMode = False
Application.CutCopyMode = False
Rng.Columns(RowColumn).ClearContents
End With
Application.EnableEvents = True
End Sub
Sub UpdateMaster()
' 28 Sep 2017
Dim Master As Worksheet
Dim Rl As Long
Dim RsltColumn As Long, RowColumn As Long
Dim R As Long
Set Master = Worksheets("Master")
RsltColumn = ResultColumn(Master, RowColumn)
Application.EnableEvents = False
With ActiveSheet
Rl = .Cells(.Rows.Count, "A").End(xlUp).Row
For R = 2 To Rl
Master.Cells(.Cells(R, RowColumn).Value, RsltColumn).Value = .Cells(R, RsltColumn).Value
Next R
End With
Application.EnableEvents = True
End Sub
Please name your master worksheet "Master" or change the name by which this worksheet is referred to in the code to whatever you name that worksheet. It should have a proper name.
Run the procedure "CreateAgentCopies" to create new worksheets for all agents. If the sheets already exist all data will be deleted from them, else they will be created.
In this process a column is added in which the program writes the row numbers. This extra column is copied to the agents sheets where it is hidden. On the Master it is deleted after copying.
Run the procedure "UpdateMaster" to copy results from the agent sheets to the Master. This procedure refers to the "ActiveSheet", meaning you must select the agents sheet you wish to update from, perhaps inspect it, and then run the code.
The function "ResultColumn" is for use of the other two procedures and intended to join the column management in one place. Please read my comments in this function. You can define the ResultColumn in some other way, like the "3rd column from the right". I think you can't define is by counting from the left because the number of your columns varies. But you must define the RowColumn relative to the ResultColumn. Since it is never visible, you can place it very far from the others, if you like.
