"Remove Duplicates" macro gives error on protected worksheet

Closed
Pluggie Posts 13 Registration date Monday March 11, 2013 Status Member Last seen August 14, 2013 - Mar 26, 2013 at 08:15 AM
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 - Mar 26, 2013 at 06:38 PM
Hello,





I have a macro that sorts and removes duplicates from a fixed selection in a single column.
The cells A1:O1 (headers) and O2:O15 (calculated cells) are blocked. The rest of the sheet is unblocked.
My macro runs smoothly when the sheet is unprotected, but when I protect the sheet, so vital cell-content is secure, the macro runs into a 1004 error at the following line from my code:
ActiveSheet.Range("$A$2:$A$300").RemoveDuplicates Columns:=1, Header:=xlNo


Here is the complete code.
My question is: Can this macro do what I want in protected mode or not?
If yes, what should I change?

Sub Ontdubbelen()
'
    Range("A2:A300").Select
    ActiveWorkbook.Worksheets("PIM-Batch").Sort.SortFields.Add Key:=Range("A2"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("PIM-Batch").Sort
        .SetRange Range("A2:A300")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.Range("$A$2:$A$300").RemoveDuplicates Columns:=1, Header:=xlNo
    Selection.NumberFormat = "0"
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Arial"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
End Sub

1 reply

Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Mar 26, 2013 at 06:38 PM
Hi Pluggie,

You are receiving this error message because your sheet is password protected. I have added the part of code that will unlock the sheet and then lock it again after executing the macro. Please check the below mentioned link to download the sample sheet:

http://speedy.sh/5FPW8/MacroProtect.xlsm

The password for the sheet is: ZOHAIB

Please revert for clarification.
0