Build supplementary sheets which reference master sheet data

[leebauman]

Hello. I have a master file with the following data:
Excel 2016 (Windows) 32 bit
[Table="width:, class:head"]
[tr=bgcolor:rgb(255, 255, 255)][td]

Date​

[/td][td]

Customer​

[/td][td]

Agent​

[/td][td]

Notes​

[/td][/tr]

[tr=bgcolor:rgb(255, 255, 255)][td]

1/1/2018​

[/td][td]AAA[/td][td]Bob[/td][td]Blah Blah[/td][/tr]

[tr=bgcolor:rgb(255, 255, 255)][td]

1/1/2018​

[/td][td]BBB[/td][td]Bob[/td][td]Blee Blee[/td][/tr]

[tr=bgcolor:rgb(255, 255, 255)][td]

1/1/2018​

[/td][td]CCC[/td][td]Jan[/td][td]Na Na[/td][/tr]

[tr=bgcolor:rgb(255, 255, 255)][td]

1/1/2018​

[/td][td]DDD[/td][td]Kim[/td][td]Okay[/td][/tr]

[tr=bgcolor:rgb(255, 255, 255)][td]

1/2/2018​

[/td][td]EEE[/td][td]Bill[/td][td]When?[/td][/tr]

[tr=bgcolor:rgb(255, 255, 255)][td]

1/2/2018​

[/td][td]AAA[/td][td]Bob[/td][td]Where?[/td][/tr]

[tr=bgcolor:rgb(255, 255, 255)][td]

1/3/2018​

[/td][td]BBB[/td][td]Jan[/td][td]Why?[/td][/tr]

[tr=bgcolor:rgb(255, 255, 255)][td]

1/3/2018​

[/td][td]CCC[/td][td]Kim[/td][td]How? [/td][/tr]

[tr=bgcolor:rgb(255, 255, 255)][td]

1/3/2018​

[/td][td]DDD[/td][td]Bob[/td][td]If[/td][/tr]

[tr=bgcolor:rgb(255, 255, 255)][td]

1/4/2018​

[/td][td]AAA[/td][td]Kim[/td][td]Then[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

I want to create Agent-specific supplementary sheets (one for each Agent) within the same workbook. I need the Agent sheets to automatically update when the master sheet is updated.

So, in this example, there would be 4 supplementary sheets (Bob, Jan, Kim, Bill). Bob's sheet would look like:
Excel 2016 (Windows) 32 bit
[Table="width:, class:head"]
[tr=bgcolor:rgb(255, 255, 255)][td]

Date​

[/td][td]

Customer​

[/td][td]

Agent​

[/td][td]

Notes​

[/td][/tr]

[tr=bgcolor:rgb(255, 255, 255)][td]

1/1/2018​

[/td][td]AAA[/td][td]Bob[/td][td]Blah Blah[/td][/tr]

[tr=bgcolor:rgb(255, 255, 255)][td]

1/1/2018​

[/td][td]BBB[/td][td]Bob[/td][td]Blee Blee[/td][/tr]

[tr=bgcolor:rgb(255, 255, 255)][td]

1/2/2018​

[/td][td]AAA[/td][td]Bob[/td][td]Where?[/td][/tr]

[tr=bgcolor:rgb(255, 255, 255)][td]

1/3/2018​

[/td][td]DDD[/td][td]Bob[/td][td]If[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

I appreciate any help!

 

[Johnny Thunder]

Well, here is a start for your project.

This code will add in sheets based on the Agent Name and only add 1 sheet even if the name is repeated.

Sub AddMissingSheets()


Dim rngMyRange As Range, rngCell As Range
Dim sht As Worksheet, Sourcesht As Worksheet
Dim SheetName As String


Set Sourcesht = Sheets("Sheet1")
Application.ScreenUpdating = False




With Sourcesht
Sourcesht.Select
Set rngMyRange = .Range(.Range("A1"), .Range("a1").End(xlDown)) 'Range with Names




    For Each rngCell In rngMyRange


        'rngCell.EntireRow.Select
        
        If (SheetExists(rngCell.Value)) Then
            
            
        Else
            Sheets.Add After:=Sheets("Sheet1")
            ActiveSheet.Name = rngCell.Value
            ActiveWindow.DisplayGridlines = False
            
        End If


        'Go back to the DATA sheet
        Sourcesht.Select
    
    Next


End With




End Sub


Function SheetExists(shtName As String, Optional wb As Workbook) As Boolean
    Dim sht As Worksheet


     If wb Is Nothing Then Set wb = ThisWorkbook
     On Error Resume Next
     Set sht = wb.Sheets(shtName)
     On Error GoTo 0
     SheetExists = Not sht Is Nothing
 End Function

 

[leebauman]

Terrific. Thank you.

 

[mumps]

I think that this is a two step process. Place the first macro in a regular module and run it once. It will create a sheet for each existing agent and copy their respective data.

Sub CreateSheets()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim agent As Range
    Dim ws As Worksheet
    Dim rngUniques As Range
    Sheets("Sheet1").Range("C1:C" & LastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
        ("C1:C" & LastRow), Unique:=True
    Set rngUniques = Sheets("Sheet1").Range("C2:C" & LastRow).SpecialCells(xlCellTypeVisible)
    If Sheets("Sheet1").AutoFilterMode = True Then Sheets("Sheet1").AutoFilterMode = False
    For Each agent In rngUniques
        Set ws = Nothing
        On Error Resume Next
        Set ws = Worksheets(agent.Value)
        On Error GoTo 0
        If ws Is Nothing Then
                Worksheets.Add(After:=Sheets(Sheets.Count)).Name = agent.Value
                Sheets("Sheet1").Rows(1).Copy Cells(1, 1)
        End If
    Next agent
    For Each agent In rngUniques
        Sheets("Sheet1").Range("C1:C" & LastRow).AutoFilter Field:=1, Criteria1:=agent
        Sheets("Sheet1").Range("C2:C" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets(agent.Value).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        If Sheets("Sheet1").AutoFilterMode = True Then Sheets("Sheet1").AutoFilterMode = False
    Next agent
    Application.ScreenUpdating = True
 End Sub

Now you need another macro to keep the agent sheets updated or to add a new sheet for any new agent who is added to Sheet1. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. This macro will be triggered automatically when you enter an agent name in column C of Sheet1 and exit the cell. For example, let's say you want add Lucy to column C. First enter the data in all the other columns for Lucy and enter her name in column C last and exit the cell. If her sheet doesn't already exist, it will be created and her data copied over. If you enter a name that already exists, that data will be copied over automatically.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim ws As Worksheet
    Set ws = Nothing
    On Error Resume Next
    Set ws = Worksheets(Target.Value)
    On Error GoTo 0
    If ws Is Nothing Then
        Worksheets.Add(After:=Sheets(Sheets.Count)).Name = Target.Value
        Sheets("Sheet1").Rows(1).Copy Sheets(Target.Value).Cells(1, 1)
        Target.EntireRow.Copy Sheets(Target.Value).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Else
        Target.EntireRow.Copy Sheets(Target.Value).Cells(Sheets(Target.Value).Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[leebauman]

Thank you very much, this works great.

Is there a way to build this in a way that a macro doesn't need to be run in order to update the Agent sheets? I don't want to have to rely on the person entering info into the Master/Source file remembering to run the macro after each revision in order to keep the Agent sheets updated.

Thanks again!

 

[mumps]

You don't have to run that macro again. I only posted it so that it would take care of the initial creation of the agent sheets and copying their data. After you run it once to do that, all you have to do to add a new agent is fill in all the columns for that agent except column C and then fill column C last with the new name. Once you enter the name and exit the cell, the new sheet will be created and the data copied over. If you need to update information for an existing agent, make the necessary changes to columns A, B and D and then re-enter the name in column C, overwriting the previous one. You have to re-enter the same name because it is the change in Column C that triggers the macro. I hope this makes sense. If you have any questions, please let me know.
Unfortunately, if you change columns A, B and D for an existing agent, the data for that agent will be reproduced at the bottom of the page. It doesn't replace the old data. If this doesn't work out, we could look at alternatives.

 

[leebauman]

Thank you for the detail. So, the data on the Master/Source file is a daily log of activity and new entries will be made every day (old entries will not be removed/deleted). I think this causes an issue with the approach you're suggesting where existing entries are edited and the Agent name re-entered. So, my objective is to add row after row of info as business activity comes in and have the Agent sheets update dynamically. Hope that makes sense.

Thank you again for the attention.

 

[mumps]

It does make sense but for the update to be made automatically, you need an event macro such as the WorkSheet_Change macro I suggested. This macro is triggered by a change in a cell in the sheet. In your case I chose the change to be made in column C, the agent name. This means that column C must be the last cell to be completed in each row. You can choose a different target column but whatever column you choose, that must be the last column populated in that row. Another way to do this would be to add a column at the end, column D, and name it something like "Complete". When you are ready to create the new sheet and copy the data, you could simply put an "x" in that column to trigger the macro. Would this be better for you?

 

[leebauman]

yes, that can work. this is all just example data, but I can work my "real" file to ensure the sequence of entry is such that the last entry triggers the update. very cool. thanks again!

 

[mumps]

In which column would the "x" be entered in your real file?