Use this guide to compile faculty evaluation reports across Phase 2 and 3 in Acuity One45.

If you are pulling a report from July 1 - June 30, you will need to pull 4 total Form Data Reports. One from each: Phase 2 (Year 1), Phase 2 (Year 2), Phase 3 (Year 1), Phase 3 (Year 2). This KB uses July 1, 2024 - June 30, 2025 as the example. 

To pull the first Phase 2 report, 

1. Ensure you are in the correct group and year. For Phase 2, you should be in "Phase 2 | Location | Specialty" (e.g. Phase 2 | MAD | PED)
2. Select 'Reports' on the top menu and 'Reports Center' on the left menu
3. Select 'Form Data Report'
4. Select your dates (July 1, 2024 - December 31, 2024) and the 3 Evaluation forms (Preceptor, Mentor, and Non-clinical)
5. Select 'Run Report' or export directly to Excel

Repeat these steps for the other Phase 2 report with different dates in the other year (2025).

To pull the first Phase 3 report, 

1. Ensure you are in the correct year and in the Phase 3 group
2. Select 'Reports' on the top menu and 'Reports Center' on the left menu
3. Select 'Form Data Report'
4. Select your dates (July 1, 2024 - December 31, 2024) and the 3 Evaluation forms (Preceptor, Mentor, and Non-clinical)
5. Select 'Run Report'
6. Once the report is run, you can create a filter to only include your courses. To do this, click on 'Filters'
7. Use either "Any" or "All" logic. Any is equivalent to "Or" logic, and All is equivalent to "And" logic
8. Choose the column you want to filter on (e.g. Activity Abbreviation)
9. You can select from the drop down or type to search for the column
10. Select the qualifier for the open text box (contains, does not contain, range, etc.) Enter the text you want to filter on (e.g. contains 708-)
    
    1. This example will show only courses with a 708 in the first part of their course code.
11. Export this report to Excel. 

Copy and paste all reports into one Excel document. All columns should line up. If you need individual reports, in addition to the combined report, you can run a macro in Excel to separate the information into separate sheets based on last name. To run a macro: 

1. Save your file as ".xlsm" to allow macros to be used
   1. Ensure that "Target Last Name" is in Column A
2. Enable the Developer Tab If it's not already visible:
   1. Go to File → Options → Customize Ribbon
   2. Check the box for Developer and click OK
3. Open the VBA Editor
   1. Click the Developer tab → Visual Basic
   2. In the editor, go to Insert → Module
   3. Paste the Macro Code Here's a simple example that copies columns B and D from one sheet to another:

Sub CreateSheetsAndMoveData()
    Dim wsSource As Worksheet
    Dim lastRow As Long
    Dim rng As Range, cell As Range
    Dim dict As Object
    Dim customerName As String
    Dim wsTarget As Worksheet
    Dim header As Range
    Dim copyRow As Range

    Set wsSource = ThisWorkbook.Sheets("Sheet1") ' Adjust sheet name
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    Set rng = wsSource.Range("A2:A" & lastRow)
    Set header = wsSource.Rows(1)

    Set dict = CreateObject("Scripting.Dictionary")

    Application.ScreenUpdating = False

    ' First pass: collect unique names and create sheets
    For Each cell In rng
        customerName = Trim(cell.Value)
        If Len(customerName) > 0 Then
            If Not dict.exists(customerName) Then
                dict.Add customerName, Nothing

                ' Sanitize sheet name
                customerName = Replace(customerName, "/", "-")
                customerName = Replace(customerName, "\", "-")
                customerName = Replace(customerName, ":", "-")
                customerName = Replace(customerName, "*", "")
                customerName = Replace(customerName, "?", "")
                customerName = Replace(customerName, """", "")
                customerName = Replace(customerName, "<", "")
                customerName = Replace(customerName, ">", "")
                customerName = Replace(customerName, "|", "")

                On Error Resume Next
                Set wsTarget = Sheets(customerName)
                If wsTarget Is Nothing Then
                    Set wsTarget = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
                    wsTarget.Name = Left(customerName, 31)
                    header.Copy Destination:=wsTarget.Range("A1")
                End If
                Set wsTarget = Nothing
                On Error GoTo 0
            End If
        End If
    Next cell

    ' Second pass: copy relevant rows
    For Each cell In rng
        customerName = Trim(cell.Value)
        If Len(customerName) > 0 Then
            Dim cleanName As String
            cleanName = customerName
            cleanName = Replace(cleanName, "/", "-")
            cleanName = Replace(cleanName, "\", "-")
            cleanName = Replace(cleanName, ":", "-")
            cleanName = Replace(cleanName, "*", "")
            cleanName = Replace(cleanName, "?", "")
            cleanName = Replace(cleanName, """", "")
            cleanName = Replace(cleanName, "<", "")
            cleanName = Replace(cleanName, ">", "")
            cleanName = Replace(cleanName, "|", "")

            Set wsTarget = Sheets(Left(cleanName, 31))
            Set copyRow = cell.EntireRow
            copyRow.Copy Destination:=wsTarget.Cells(wsTarget.Cells(wsTarget.Rows.Count, "A").End(xlUp).Row + 1, 1)
        End If
    Next cell

    Application.CutCopyMode = False
    Application.ScreenUpdating = True

    MsgBox "Sheets created and data sorted!"
End Sub

1. Select the macro
2. Click 'run'

You should now have sheets for each faculty member with their evaluation data.