Is there a code that can handle all this? One master splitting off into separate workbooks for each employee and department. RRS feed

  • Question

  • I currently have a Master workbook of all members in my program.  I am able to separate this out to each employee to have their own sheet.  But this is where it gets tricky.  Each employee will only be changing data in Columns N-V. Additionally I need to have a second master list for another department to handle billing information.  All workbooks and sheets will have the same header row.  Columns used are A-V in all workbooks and sheets.  Rows will differ depending on the Master sheets Column B (FileName).  I want to have it set up so I will only have to be in one sheet (Master) when entering data and not have to go into each individual file to add the appropriate rows from the master.  But each employee will be adding information in columns N-V daily and I need this data to bounce back to the Master I use and also to the Billing department sheet and vice versa.  Is there a way to inter link all these workbooks so that each employee, billing department and myself have our own sheet to work off of but can see data each department/employee enters?

    Column Titles used in all workbooks:  

    1. A - Group   ------ This is the column used to separate for Billing Department
    2. B - File Name -------This is the column used to separate for each employee's separate workbook
    3. C - CFSS/PSS/RN
    4. D - Care Coordinator
    5. E - IHH Status
    6. F - MRN#
    7. G - Last Name
    8. H - First Name
    9. I - DOB
    10. J - MCO
    11. K - MCO ID
    12. L - SID
    13. M - ICD 10 Code
    14. N - Action Date
    15. O - Chrg Entry Date
    16. P - Initials
    17. Q - Attempts
    18. R - PC/GC
    19. S - F2F/CC
    20. T - Nurse Review
    21. U - CAP/CP Date
    22. V - Receiving CSS

    Master Workbook - 1 WORKBOOK w/1 SHEET LABELED "MasterIHH" (also file name of workbook)

    1. This is where new rows will be added each month as new members join.  (Would like it to also automatically move to each individuals worksheet via the B column from the master stating what file it should be in.)
    2. If new member is approved I enter data in columns A-M.
    3. Track daily activity.
    4. Only I use this. 

    Employee sheets - 10 (Files) WORKBOOKS w/1 sheet labeled with Column B (File Name).  Will also be file name of workbook)

    1. I have separated via column B from the Master List to a file for each employee. Code below.
    2. I then save a copy of each sheet as a new workbook for each employee.
    3. Employee only enters data in Columns N, P-V.
    4. Each employee has their own sheet with their team members on it.

    Billing Department - 1 Workbook with 4/sheets labeled with Column A (Group).  (File name will be IHH Billing)

    1. Needs a workbook that would be pulled by from each employee.  This workbook would need to be separated out by 4 separate worksheets via column A and each tab titled from column A's grouping.
    2. They will only be entering data into Column O.
    3. Only billing will be using this worksheet.

    To come full circle I would like:

    1. Data the employees enter in columns N, P-V come back to the Master Sheet AND to the Billing Department workbook into the appropriate columns.
    2. Data the Billing Department enter in Column O come back to the Master workbook AND to each employee's workbook into the appropriate columns.
    3. New rows I enter on the Master workbook to go to the appropriate employee workbook AND also billing office.

    CURRENTLY THIS IS ALL I HAVE. :(  Please someone tell me this is possible.  I spend too much time going back and forth between/ sheets and departments!!!!  Keeping fingers crossed someone can help me out.  

    • Sub parse_data()
      Dim lr As Long
      Dim ws As Worksheet
      Dim vcol, a As Integer
      Dim acol As Long
      Dim myarr As Variant
      Dim title As String
      Dim titlerow As Integer
      vcol = 1
      Set ws = Sheets("allihh")
      lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
      title = "A1:V1"
      titlerow = ws.Range(title).Cells(1).Row
      acol = ws.Columns.Count
      ws.Cells(1, acol) = "Unique"
      For a = 2 To lr
      On Error Resume Next
      If ws.Cells(a, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(a, vcol), ws.Columns(acol), 0) = 0 Then
      ws.Cells(ws.Rows.Count, acol).End(xlUp).Offset(1) = ws.Cells(a, vcol)
      End If
      myarr = Application.WorksheetFunction.Transpose(ws.Columns(acol).SpecialCells(xlCellTypeConstants))
      For a = 2 To UBound(myarr)
      ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(a) & ""
      If Not Evaluate("=ISREF('" & myarr(a) & "'!A1)") Then
      Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(a) & ""
      Sheets(myarr(a) & "").Move after:=Worksheets(Worksheets.Count)
      End If
      ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(a) & "").Range("A1")
      Sheets(myarr(a) & "").Columns.AutoFit
      ws.AutoFilterMode = False
      End Sub

    • Edited by jenn12376 Wednesday, May 31, 2017 9:00 PM
    Wednesday, May 31, 2017 8:46 PM

All replies

  • Hello,

    >>Is there a way to inter link all these workbooks so that each employee, billing department and myself have our own sheet to work off of but can see data each department/employee enters?

    I think you could create a shared workbook. Since everyone has his own column to enter data, I think you may use macro to check current user who using the document and limit his permission to access the data. As the owner of the shared workbook, you can manage it by controlling user access to the shared workbook and resolving conflicting changes. You could visit Use a shared workbook to collaborate  & Share your Excel 2016 workbook with others  for detail information.

    If there are separated files for each user, to link these files to a master file, you might create connections between them. However the connections are used to get data from these separated files. So you get the data from users, but users would not receive your update in their columns. For more information, please visit Connect to (import) external data  &  Connect data in another workbook to your workbook  

    In my opinion, shared workbooks would be of great help to collaborate. Data connections would be tedious as you need a large number of connections between all workbooks.



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Thursday, June 1, 2017 7:41 AM