Excel VBA问题：按Employee ID将Sheet1排班数据映射到Sheet2指定格式

Let's fix your VBA code to match the exact column mapping you need. The original code copies entire columns directly, which doesn't account for leaving Shift2 columns empty and aligning each day's shift to the correct DayX-Shift1 column in Sheet2. Plus, nested loops and repeated sheet activation make it inefficient and error-prone.

Here's a revised, optimized version that aligns with your requirements:

Sub SyncSchedulingToSheet2()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim empIDLookup As Object
    Dim lastRow1 As Long, lastRow2 As Long
    Dim i As Long, targetRow As Long
    
    ' Set direct references to worksheets (no more activating sheets!)
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    Set empIDLookup = CreateObject("Scripting.Dictionary")
    
    ' Build a lookup dictionary: Employee ID -> Row number in Sheet2
    lastRow2 = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row
    For i = 2 To lastRow2
        empIDLookup(ws2.Cells(i, "A").Value) = i
    Next i
    
    ' Process each row of scheduling data in Sheet1
    lastRow1 = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row
    For i = 2 To lastRow1
        Dim empID As String
        empID = ws1.Cells(i, "A").Value
        
        ' Check if the employee exists in Sheet2
        If empIDLookup.Exists(empID) Then
            targetRow = empIDLookup(empID)
            
            ' Map each day's shift from Sheet1 to Sheet2's DayX-Shift1 columns
            ' Adjust these column references to match your actual sheet structure!
            ' Assumptions:
            ' Sheet1: B=Monday, C=Tuesday, D=Wednesday, E=Thursday, F=Friday, G=Saturday, H=Sunday
            ' Sheet2: C=Day1-Shift1, E=Day2-Shift1, G=Day3-Shift1, I=Day4-Shift1, K=Day5-Shift1, M=Day6-Shift1, O=Day7-Shift1
            ws2.Cells(targetRow, "C").Value = ws1.Cells(i, "B").Value ' Monday → Day1-Shift1
            ws2.Cells(targetRow, "E").Value = ws1.Cells(i, "C").Value ' Tuesday → Day2-Shift1
            ws2.Cells(targetRow, "G").Value = ws1.Cells(i, "D").Value ' Wednesday → Day3-Shift1
            ws2.Cells(targetRow, "I").Value = ws1.Cells(i, "E").Value ' Thursday → Day4-Shift1
            ws2.Cells(targetRow, "K").Value = ws1.Cells(i, "F").Value ' Friday → Day5-Shift1
            ws2.Cells(targetRow, "M").Value = ws1.Cells(i, "G").Value ' Saturday → Day6-Shift1
            ws2.Cells(targetRow, "O").Value = ws1.Cells(i, "H").Value ' Sunday → Day7-Shift1
        End If
    Next i
    
    ' Auto-fit columns for readability
    ws2.Cells.EntireColumn.AutoFit
End Sub

Key Changes & Notes: #

• Dictionary Lookup: Replaces slow nested loops with a fast O(1) lookup, which is way more efficient if you have a large number of employees.
• No Sheet Activation: Uses direct worksheet references instead of Activate/Select, making the code more reliable and faster.
• Explicit Column Mapping: Clearly assigns each day's shift to the correct DayX-Shift1 column in Sheet2, leaving all Shift2 columns blank as requested.
• Customizable Columns: If your Sheet1/Sheet2 column positions are different (e.g., Day1-Shift1 is column D instead of C), just update the column letters in the assignment lines.

Quick Setup: #

1. Double-check that the column mappings match your actual sheet structure.
2. Run the SyncSchedulingToSheet2 macro directly from the VBA editor or assign it to a button in Excel.

内容的提问来源于stack exchange，提问作者Rocky