M365 Automated Associate Transfer Workflow

Implementation guide for creating a centralized, zero-duplicate transfer process using SharePoint Lists, MS Forms, Excel VBA, and Power Automate.

Architecture Overview

Excel VBA Macro

➔

SharePoint Folder

➔

Flow 1 (Import Data)

⬇

SharePoint List Dashboard

➔

Email Sent to Supervisor

⬇

MS Form (Pre-filled)

➔

Flow 2 (Update Status)

➔

COMPLETED

Step 1: Create the SharePoint List

Create a Blank List on SharePoint named Associate Transfers with these columns:

Column Name	Type	Details
Title	Single line of text	Rename to Associate Name
Old Department	Single line of text	Current department info
New Department	Single line of text	Target position info
Meets HR Criteria	Single line of text	YES / NO
Supervisor Email	Single line of text / Person	Recipient email address
Status	Choice	Pending / Completed
Form Link	Hyperlink	Unique generated MS Form URL

Step 2: Create the Microsoft Form

Create a new Form named Associate Transfer Evaluation.
Add a Text Question at the top named SharePoint Record ID.
Click ... (More options) > Get pre-filled URL.
Enter [RECORD_ID] in the SharePoint Record ID answer field, then copy the generated link.

Step 3: Update Excel VBA Macro

Add this snippet to your macro to format data into a Table and save it directly to your synced SharePoint folder:

Sub ExportToSharePointFolder()
    Dim wb As Workbook, ws As Worksheet
    Dim savePath As String, fileName As String
    
    Set wb = ActiveWorkbook
    Set ws = wb.Sheets("TransferSheet")
    
    ' 1. Ensure Range is a Named Table (Required for Power Automate)
    On Error Resume Next
    ws.ListObjects.Add(xlSrcRange, ws.UsedRange, , xlYes).Name = "TransferData"
    On Error GoTo 0
    
    ' 2. Path to Local Synced SharePoint Folder
    savePath = "C:\Users\" & Environ("USERNAME") & "\YourCompany\SharePointSite - Documents\"
    fileName = "TransferData_" & Format(Now(), "YYYYMMDD_HHMMSS") & ".xlsx"
    
    ' 3. Save copy to cloud
    wb.SaveCopyAs savePath & fileName
    MsgBox "Data exported! Processing transfers...", vbInformation, "Success"
End Sub

Step 4: Power Automate Flow 1 (Import & Notify)

Trigger: SharePoint - When a file is created (properties only).
Action 1: Excel Online - List rows present in a table (Table Name: TransferData).
Action 2 (Loop): For each row:
- Create SharePoint Item (Set status to Pending).
- Generate pre-filled Form URL appending the SharePoint Item ID.
- Send Outlook Email to Supervisor with the direct Form Link.

Step 5: Power Automate Flow 2 (Update Status)

Trigger: MS Forms - When a new response is submitted.
Action 1: MS Forms - Get response details.
Action 2: SharePoint - Update item:
- Match ID with the SharePoint Record ID form answer.
- Change Status to Completed.
- Record Completed By email and submission date.

Key Outcome

Supervisors now view a single, live list. When one submits a form, the row instantly marks as COMPLETED, preventing duplicate submissions and giving HR complete real-time visibility.