我有一个macro,可以将rows从一个sheet复制到另一个。但是,我想保护此sheet使其免受编辑。但是,执行此操作时,我无法运行macro。我已经尝试了不同的锁定属性,但仍然无法正常工作。

这是我尝试过的:

Option Explicit

Dim pwd As String
Dim ws As Worksheet
pwd = "password"

ws.Unprotect password:=pwd
Next ws

Sub FilterAndCopy()
    Dim rng As Range, sht1 As Worksheet, sht2 As Worksheet

    Set sht1 = Worksheets("SHIFT LOG")
    Set sht2 = Worksheets("CHANGE OF NO'S")

    sht2.UsedRange.ClearContents

    With Intersect(sht1.Columns("B:BP"), sht1.UsedRange)
        .Cells.EntireColumn.Hidden = False
        If .Parent.AutoFilterMode Then .Parent.AutoFilterMode = False

        .AutoFilter Field:=1, Criteria1:="Change of Numbers"

        .Range("A:F, AD:AD, BL:BO").Copy Destination:=sht2.Cells(4, "B")
        .Parent.AutoFilterMode = False

        .Range("F:AD").EntireColumn.Hidden = True
        .Range("AE:BK").EntireColumn.Hidden = True
   End With

    'Go to last cell in column B
    With ActiveSheet
    Range("B5").Select
    Selection.End(xlDown).Select
    End With

End Sub

ws.Protect password:=pwd
Next ws

最佳答案

在您的代码之前和之后尝试此代码段代码

之前:
'sheet1和sheet2受保护

Dim pwd As String
Dim ws as WorkSheet
pwd = "password"

For Each ws In Worksheets
    ws.Unprotect Password:=pwd
Next ws


'您的代码

后:

For Each ws In Worksheets
    ws.protect Password:=pwd
Next ws


希望这可以帮助

评论后编辑帖子

Option Explicit

Sub FilterAndCopy()

Dim pwd As String
Dim sht1, sht2 As Worksheet
Dim rng As Range

pwd = "password"

Set sht1 = Worksheets("SHIFT LOG")
Set sht2 = Worksheets("CHANGE OF NO'S")

sht2.Unprotect Password:=pwd 'unprotect the sheet
sht2.UsedRange.ClearContents ' clear contents

With Intersect(sht1.Columns("B:BP"), sht1.UsedRange)

    .Cells.EntireColumn.Hidden = False
    If .Parent.AutoFilterMode Then .Parent.AutoFilterMode = False

    .AutoFilter Field:=1, Criteria1:="Change of Numbers"

    .Range("A:F, AD:AD, BL:BO").Copy Destination:=sht2.Cells(4, "B")
    .Parent.AutoFilterMode = False


    .Range("F:AD").EntireColumn.Hidden = True
    .Range("AE:BK").EntireColumn.Hidden = True

End With

'Go to last cell in column B
With ActiveSheet

    Range("B5").Select
    Selection.End(xlDown).Select

End With

sht2.Protect Password:=pwd ' protect the sheet


End Sub

09-10 13:27
查看更多