请求优化VBA宏：提升跨工作表复制值并升序排序的运行效率

Hey there! Let's tackle that slow VBA code issue you're facing. The main culprits here are the clipboard-based copy-paste and unnecessary UI/interaction overhead. Here's an optimized version of your code that should drastically cut down execution time, plus a breakdown of the key improvements:

Key Optimizations Made: #

• Replaced Copy/Paste with Direct Value Assignment: Skipping the clipboard entirely is the biggest win here—clipboard operations are surprisingly slow for large datasets, especially when repeated.
• Disabled UI & Background Overhead: Turned off screen updating, events, and automatic calculations during the process to eliminate unnecessary system overhead.
• Removed Select/Selection: These methods force Excel to interact with the UI, which adds significant lag. We work directly with range objects instead.
• Streamlined Sorting: Target the exact data range for sorting instead of relying on CurrentRegion.Select.

Optimized VBA Code #

Private Sub CommandButton1_Click()
    Dim wsDetail As Worksheet
    Dim wsArranged As Worksheet
    Dim sourceRange As Range
    Dim targetRange As Range
    Dim lastRowDetail As Long
    Dim lastRowArranged As Long
    
    ' Disable overhead features to speed up execution
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    ' Set worksheet references (easier to read and maintain)
    Set wsDetail = ThisWorkbook.Sheets("Employee Access Detail")
    Set wsArranged = ThisWorkbook.Sheets("Employee Access Arranged")
    
    ' Refresh all data connections first
    ThisWorkbook.RefreshAll
    
    ' Clear existing data in the arranged sheet (A2:D)
    wsArranged.Range("A2:D" & wsArranged.Cells(wsArranged.Rows.Count, "A").End(xlUp).Row).ClearContents
    
    ' Get the last row with data in the detail sheet (columns A-D)
    lastRowDetail = wsDetail.Cells(wsDetail.Rows.Count, "D").End(xlUp).Row
    ' Define the source range (A4 to last row in D)
    Set sourceRange = wsDetail.Range("A4:D" & lastRowDetail)
    
    ' Get the first empty row in column A of the arranged sheet
    lastRowArranged = wsArranged.Cells(wsArranged.Rows.Count, "A").End(xlUp).Row + 1
    ' Define target range matching the size of the source range
    Set targetRange = wsArranged.Range("A" & lastRowArranged).Resize(sourceRange.Rows.Count, sourceRange.Columns.Count)
    
    ' Directly assign values (no clipboard!)
    targetRange.Value = sourceRange.Value
    
    ' Sort the data by column C in ascending order
    With wsArranged.Sort
        .SortFields.Clear
        .SortFields.Add Key:=wsArranged.Range("C2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange wsArranged.Range("A2:D" & wsArranged.Cells(wsArranged.Rows.Count, "A").End(xlUp).Row)
        .Header = xlYes ' Assuming row 1 is header; adjust to xlNo if no header
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    ' Activate the arranged sheet and move to A2 (if still needed)
    wsArranged.Activate
    wsArranged.Range("A2").Activate
    
    ' Restore application settings
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Additional Notes: #

• If your "Employee Access Arranged" sheet doesn't have a header row, change .Header = xlYes to .Header = xlNo in the sort section.
• The direct value assignment (targetRange.Value = sourceRange.Value) is lightning fast compared to copy-paste because it transfers data directly in memory, with no intermediate clipboard step.
• By disabling screen updating, you prevent Excel from redrawing the sheet every time data changes, which is a huge speed saver for large datasets.

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