时间戳

首页 > 解决方案 > 复制工作表粘贴为值但跳过受保护的单元格

vba - 复制工作表粘贴为值但跳过受保护的单元格

问题描述

我正在循环浏览几张纸，并想删除我不想要的那些。对于我想保留的那些，我正在复制所有内容并粘贴为值。问题是有一些我无法保护的受保护单元格，这给了我一个错误。如何避免？

Sub save()

Dim wb As Workbook
Dim path As String
Dim fname As String
Dim fdate As Date

' picks up the date of the reporting period so it uses it for naming the new workbook
fdate = Sheets("Instructions").Range("D1").Value
Sheets("Introduction").Range("F9").Copy
Sheets("Introduction").Range("F9").PasteSpecial xlPasteValues
Sheets("Introduction").Range("F10").Copy
Sheets("Introduction").Range("F10").PasteSpecial xlPasteValues

Application.DisplayAlerts = False
'FOR EACH SHEET IN THE WORKBOOK THAT IS ONE OF THE 5 ONES WE WANT TO SAVE IS COPIES AND PASTES AS VALUES AND DELETES THE ONES THAT ARE NOT NAMES AS BELOW
For Each Sheet In ActiveWorkbook.Sheets
    If Sheet.Name = "Introduction" Or Sheet.Name = "Instructions" Or Sheet.Name = "Results" Then
        Sheet.Select
        Cells.Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Selection.Locked = True
        Sheet.Range("D2") = fdate

        ActiveSheet.Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True
    Else
        'if sheet is not one of the X it gets deleted
        Sheet.Delete
    End If
Next

' adding the name we wish to give the new workbook
fname = Sheets("Introduction").Range("F7") & "Result" & fdate
path = Application.ActiveWorkbook.path
'ActiveWorkbook.Protect Password:="password", Structure:=True, Windows:=False


'saves the workbook as the name we chose and date
Application.ActiveWorkbook.SaveAs Filename:=path & "\" & fname, _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

'close the workbook
ActiveWindow.Close
Application.DisplayAlerts = True


End Sub

我尝试了一些代码，例如：

for each cell in sheet 
   if Not cell.Locked then
     cell.copy
     cell.pastespecial xlpastevalues
   end if
next

但不工作，它给了我一个错误，“对于工作表中的每个单元格”错误 438，即未定义对象或类似的东西。

有什么建议么？

标签： vbapasteprotectedcellsskip

解决方案

缺少单元格的声明，接下来您需要工作表中的一个范围来循环遍历（.UsedRange 循环遍历工作表中所有使用的单元格）。这应该有效：

Dim cell As Range
for each cell in sheet.UsedRange
   if Not cell.Locked then
     cell.copy
     cell.pastespecial xlpastevalues
   end if
next

推荐阅读