VBA实现粘贴至Sheet1时跳过锁定单元格的解决方案

我完全懂你遇到的麻烦——辛辛苦苦把黄色单元格（色值6）锁定保护起来，结果粘贴Sheet2内容时直接报错，还没法只更新未锁定的区域。别担心，下面这套方案既能保留锁定单元格的完整性，又能精准粘贴到未锁定区域，全程不用手动干预。

核心思路 #

直接整区域粘贴会触发保护错误，所以我们换个更灵活的思路：

• 先把Sheet2要复制的区域内容读取到内存数组里（比逐个单元格读取快N倍，大区域场景尤其明显）
• 遍历Sheet1目标区域的每个单元格，只对未锁定的单元格写入数组对应的值
• 全程不需要解除工作表保护（只要保护时勾选了「选定未锁定单元格」，写入未锁定单元格是允许的）

完整VBA代码 #

Sub PasteSkipLockedCells()
    Dim sourceRange As Range
    Dim targetRange As Range
    Dim sourceData As Variant
    Dim i As Long, j As Long
    
    ' --------------------------
    ' 步骤1：锁定黄色单元格（色值6）并保护工作表
    ' --------------------------
    With ThisWorkbook.Sheets("Sheet1")
        ' 先解除保护（如果之前已保护）
        .Unprotect Password:="你的密码" ' 无密码可直接删除Password参数
        
        ' 遍历所有单元格，锁定黄色（色值6）单元格，其余设为未锁定
        For Each cell In .UsedRange
            cell.Locked = (cell.Interior.ColorIndex = 6)
        Next cell
        
        ' 重新保护工作表，允许操作未锁定单元格
        .Protect Password:="你的密码", AllowSelectingUnlockedCells:=True
    End With
    
    ' --------------------------
    ' 步骤2：从Sheet2复制指定区域内容到数组
    ' --------------------------
    Set sourceRange = ThisWorkbook.Sheets("Sheet2").Range("A1:D10") ' 替换为你的源区域
    sourceData = sourceRange.Value ' 把区域内容存入数组，提升效率
    
    ' --------------------------
    ' 步骤3：粘贴到Sheet1目标区域，跳过锁定单元格
    ' --------------------------
    Set targetRange = ThisWorkbook.Sheets("Sheet1").Range("A1:D10") ' 替换为你的目标区域
    
    ' 检查源区域和目标区域大小是否匹配
    If sourceRange.Rows.Count <> targetRange.Rows.Count Or _
       sourceRange.Columns.Count <> targetRange.Columns.Count Then
        MsgBox "源区域和目标区域大小不一致，请检查！"
        Exit Sub
    End If
    
    ' 遍历每个单元格，仅写入未锁定的单元格
    For i = 1 To UBound(sourceData, 1)
        For j = 1 To UBound(sourceData, 2)
            With targetRange.Cells(i, j)
                If Not .Locked Then
                    .Value = sourceData(i, j)
                    ' 如需复制格式，取消注释下面两行
                    '.Font = sourceRange.Cells(i, j).Font
                    '.Interior.Color = sourceRange.Cells(i, j).Interior.Color
                End If
            End With
        Next j
    Next i
    
    MsgBox "粘贴完成！已自动跳过所有锁定单元格。"
End Sub

关键细节说明 #

1. 工作表保护设置：AllowSelectingUnlockedCells:=True 是核心，确保保护状态下仍能操作未锁定单元格
2. 数组优化：把源区域内容存入数组避免了频繁读写Excel单元格，运行速度会快很多
3. 锁定逻辑简化：用cell.Locked = (cell.Interior.ColorIndex = 6)一行代码完成锁定判断，简洁高效
4. 格式可选复制：如果需要同步粘贴字体、背景色等格式，只需取消注释代码里的格式复制行即可

注意事项 #

• 记得把代码里的"你的密码"替换成实际的工作表保护密码，无密码可直接删除该参数
• 源区域和目标区域的行列数必须一致，否则会弹出提示
• 若黄色单元格不在UsedRange范围内，可以调整遍历范围（比如指定特定区域Range("A1:Z100")）

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