VBA动态范围复制粘贴报错：运行时错误'1004'排查求助

问题根源 #

• 变量赋值语法错误：你给rng赋值的写法不符合VBA规范——给Range对象赋值必须用Set关键字，而Range(...).Select只是选中单元格，不是把范围对象赋值给变量。这行代码本身就会触发错误，根本到不了后面的If判断环节。
• 无效列引用：当日志只有8分钟数据（即8列）时，Cells(1, Columns.Count).End(xlToLeft)获取的是第8列，再执行Offset(0,-10)会得到列号-2，Excel不存在负数列，直接触发1004错误，If判断完全没机会生效。
• 判断逻辑无效：IsEmpty(rng)是用来判断单元格内容是否为空，但这里rng要么没正确赋值，要么指向了不存在的范围，这个判断根本起不到规避错误的作用。

修正后的代码 #

Sub tenMinBack()
    Dim logSheet As Worksheet
    Dim pricesSheet As Worksheet
    Dim lastCol As Integer
    Dim targetCol As Integer
    Dim rng As Range
    
    ' 关闭冗余操作，提升运行效率
    Application.CutCopyMode = False
    Application.ScreenUpdating = False
    
    ' 直接绑定工作表，避免用Select/Activate，减少状态类错误
    Set logSheet = ThisWorkbook.Worksheets("Log")
    Set pricesSheet = ThisWorkbook.Worksheets("Prices")
    
    ' 获取Log表的最后一列序号
    lastCol = logSheet.Cells(1, logSheet.Columns.Count).End(xlToLeft).Column
    ' 计算10分钟前数据所在的目标列
    targetCol = lastCol - 10
    
    ' 先判断目标列是否存在（列号≥1）
    If targetCol >= 1 Then
        ' 正确赋值要提取的范围
        Set rng = logSheet.Range(logSheet.Cells(1, targetCol), logSheet.Cells(13, targetCol))
        ' 直接赋值替代复制粘贴，更高效且无剪贴板冲突
        pricesSheet.Range("D3:D15").Value = rng.Value
        
        ' 自动调整列宽
        logSheet.Cells.EntireColumn.AutoFit
        pricesSheet.Cells.EntireColumn.AutoFit
    End If
    
    ' 恢复屏幕更新
    Application.ScreenUpdating = True
End Sub

核心改进说明 #

• 摒弃Select/Activate操作，直接绑定工作表对象，这是VBA的最佳实践，能避免绝大多数因选中状态异常导致的错误。
• 先计算目标列号并判断其有效性（≥1），从根源上杜绝无效列引用的问题。
• 用Value = Value直接赋值替代复制粘贴，不仅运行效率更高，还能避免剪贴板占用的问题。
• 严格按照VBA语法规范使用Set为Range对象赋值，避免语法错误。

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