如何提升VBA复制粘贴至动态增长Excel工作表的运行速度？

Hey there! I see you're stuck with a glacial VBA macro that takes 8 minutes to copy a single cell's value to the dynamic N column in your Checklists sheet—let's fix that speed issue right away.

Why Your Original Code Is Slow #

The biggest performance killers here are:

• Repeated Select/Selection calls, which force Excel to refresh the UI every time you switch sheets
• Likely a row-by-row paste loop (implied by the slow runtime with 3000+ weekly rows), which wastes cycles on redundant operations

Optimized, Blazing-Fast Solution #

This code cuts out all the unnecessary UI overhead and uses direct value assignment instead of copy-paste to handle the entire column in one go:

Sub CopyDB10ToChecklistsN()
    Dim wsPivot As Worksheet
    Dim wsChecklist As Worksheet
    Dim lastRow As Long
    Dim targetRange As Range
    
    ' Disable resource-heavy Excel features temporarily
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    ' Set direct references to your worksheets (no more switching/selecting!)
    Set wsPivot = ThisWorkbook.Sheets("PivotTables")
    Set wsChecklist = ThisWorkbook.Sheets("Checklists")
    
    ' Find the last used row in Checklists (adjust "A" to your data column if needed)
    lastRow = wsChecklist.Cells(wsChecklist.Rows.Count, "A").End(xlUp).Row
    
    ' Define the full target range in column N
    Set targetRange = wsChecklist.Range("N1:N" & lastRow)
    
    ' Assign the value directly (no clipboard, no looping—just one operation!)
    targetRange.Value = wsPivot.Range("DB10").Value
    
    ' Restore Excel's normal behavior
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    
    MsgBox "Value copied to column N successfully!", vbInformation
End Sub

Key Improvements Breakdown #

• No more Select/Selection: We reference worksheets directly, eliminating all UI refresh delays from switching between sheets.
• Single-step value assignment: Instead of copying/pasting (which uses the slow clipboard) or looping through rows, we set the entire column's value in one shot—this alone will reduce runtime from minutes to seconds.
• Temporarily disabled background processes: Turning off screen updates, events, and automatic calculation stops Excel from doing unnecessary work while the macro runs. We restore these settings at the end so Excel behaves normally afterward.
• Dynamic row detection: The code automatically finds the last used row in Checklists, so it works perfectly even as 3018 rows get added each week.

内容的提问来源于stack exchange，提问作者Damon. Martin