Excel VBA实现带迭代的自适应区域值跨表复制粘贴

以下代码无需手动指定复制/粘贴范围，自动适配不同长度的数据源，支持自定义迭代次数，针对万次级迭代做了性能优化：

Sub MonteCarloBatchTransposePaste()
    ' 临时调整Excel设置提升运行速度
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Dim wsSource As Worksheet, wsTarget As Worksheet
    Dim sourceCol As Long, validDataCount As Long
    Dim iterationCount As Long, pasteStartRow As Long
    Dim dataStartRow As Long
    Dim i As Long
    
    ' -------------------------- 自定义参数区 按需修改 --------------------------
    Set wsSource = ThisWorkbook.Sheets("sheet1")  ' 存储单列数据源的工作表
    Set wsTarget = ThisWorkbook.Sheets("sheet2")  ' 存储转置后批量结果的工作表
    sourceCol = 1                                  ' 数据源所在列，1=A列、2=B列，以此类推
    iterationCount = 10000                         ' 迭代运行次数，按需调整
    dataStartRow = 2                               ' 数据源起始行，有表头填2，无表头填1
    ' -------------------------------------------------------------------------
    
    ' 自动统计数据源列的有效值总数
    validDataCount = wsSource.Cells(wsSource.Rows.Count, sourceCol).End(xlUp).Row - dataStartRow + 1
    
    ' 自动定位目标表首个空白行作为粘贴起点，不会覆盖历史数据
    pasteStartRow = wsTarget.Cells(wsTarget.Rows.Count, 1).End(xlUp).Row + 1
    
    ' 循环批量写入值
    For i = 1 To iterationCount
        ' 触发工作表重算，更新蒙特卡洛模拟生成的动态值
        Application.Calculate
        ' 直接数组转置赋值，跳过剪贴板，比Copy/Paste效率高10倍以上
        wsTarget.Cells(pasteStartRow + i - 1, 1).Resize(1, validDataCount).Value = _
            Application.Transpose(wsSource.Cells(dataStartRow, sourceCol).Resize(validDataCount, 1).Value)
    Next i
    
    ' 恢复Excel默认设置
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
    MsgBox "执行完成，共生成" & iterationCount & "行结果", vbInformation
End Sub

使用说明 #

• 所有需要调整的参数全部集中在代码标注的自定义参数区，无需修改核心逻辑
• 自动识别数据源长度：无论单列数据源有10条、50条还是上百条有效值，代码都会自动统计数量，匹配对应宽度的粘贴区域，无需手动枚举列标
• 自动定位粘贴位置：每次运行会自动识别目标表最后一行已有数据，从下一个空白行开始写入，不会覆盖历史结果
• 转置逻辑内置：自动把单列多行的源数据转为单行多列格式写入目标表，符合横行粘贴的需求
• 性能优化：采用数组直接赋值代替剪贴板复制粘贴操作，万次级迭代运行时间可压缩到数秒，不会出现剪贴板占用、程序假死问题

适配调整提示 #

• 如果你的数据源没有表头、第一行就是有效数据，把dataStartRow参数改为1即可
• 如果不需要每次迭代重算（比如源数据是固定值），可以注释掉Application.Calculate行，运行速度会进一步提升
• 如果需要把结果粘贴到目标表的固定起始行（比如固定从第1行开始），直接把pasteStartRow赋值为你需要的行号即可，比如pasteStartRow = 1

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