Excel VBA编辑单元格后复制工作表至其他表时出现运行时错误1004

错误原因 #

你遇到的1004错误，核心问题是过度依赖Select/Activate方法——VBA中Select操作要求目标对象所在的工作表必须处于激活状态，而且Worksheet_Change事件触发时，Excel的上下文可能限制了某些选择操作。此外原代码逻辑存在冗余，还可能触发循环事件（复制粘贴会再次触发Change事件）。

修复后的代码 #

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tempSheet As Worksheet
    Dim mainSheet As Worksheet
    Dim newButton As Button
    
    ' 禁用事件，防止复制粘贴触发循环
    Application.EnableEvents = False
    
    Set mainSheet = Me ' 当前触发事件的工作表，即MainSheet
    Set tempSheet = ThisWorkbook.Sheets("Tempsheet")
    
    ' 清空Tempsheet旧内容（包括旧按钮）
    tempSheet.Cells.Clear
    tempSheet.Buttons.Delete
    
    ' 直接复制整个工作表内容到Tempsheet，无需Select
    mainSheet.Cells.Copy Destination:=tempSheet.Range("A1")
    
    ' 添加按钮，直接创建并设置属性，不用Select
    Set newButton = tempSheet.Buttons.Add(388.5, 47, 42, 17.5)
    ' 可按需设置按钮文本，比如：newButton.Caption = "返回主表"
    
    ' 回到原工作表
    mainSheet.Activate
    
    ' 恢复事件
    Application.EnableEvents = True
End Sub

关键优化点 #

• 移除所有Select/Activate操作：直接通过对象引用完成复制、创建按钮等操作，避免上下文依赖导致的错误。
• 禁用事件防止循环：Worksheet_Change事件触发后，复制粘贴操作会再次触发该事件，用Application.EnableEvents = False阻断循环，执行完逻辑后恢复。
• 清空旧内容：每次复制前清空Tempsheet的单元格和旧按钮，避免数据堆积。
• 明确对象引用：用变量绑定工作表和按钮，代码更清晰，减少出错概率。

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