Shared workbook and VBA

[p4nny]

Hi

I have a workbook that contains VBA and works fine as "exclusive only". The moment it is shared I get "Object Required" error when the workbook opens.

here's my code, however it is basically just filtering and copy/pasting. Any help would be greatly appreciated.

Private Sub Workbook_Open()
Set sh = ThisWorkbook.Worksheets("action plan")
    Set Sh2 = ThisWorkbook.Worksheets("ActionReqAttn")
    Dim lastrow As Long
    Application.ScreenUpdating = False
    sh.Unprotect
    Sh2.Unprotect
   
    On Error Resume Next
    sh.ShowAllData
  Sh2.Range("A:za").Clear
   
  With sh.Range("r1").CurrentRegion
 
  .AutoFilter
      .AutoFilter Field:=18, Criteria1:= _
"=Not on Track", Operator:=xlOr, Criteria2:="=Overdue"
    End With
 
    'sh.UsedRange.SpecialCells(xlCellTypeVisible).Copy _
    'Destination:=Sh2.Range("a1").End(xlDown).Row + 1
   
      sh.UsedRange.SpecialCells(xlCellTypeVisible).Copy _
        Destination:=Sh2.Range("A1")
sh.ShowAllData
       
   With sh.Range("p1").CurrentRegion
        .AutoFilter
        .AutoFilter Field:=16, Criteria1:=">=" & CLng(Date), Operator:=xlAnd, Criteria2:="<=" & CLng(Date + 30)
    End With
   
    sh.UsedRange.SpecialCells(xlCellTypeVisible).Copy _
      Destination:=Sh2.Range("A10000")
Sh2.Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  
         Sh2.Range("$A$1:$Ae$30000").RemoveDuplicates Columns:=2, Header:= _
        xlYes
   
        Sh2.Sort.SortFields.Clear
    Sh2.Sort.SortFields.Add Key:=Range( _
        "A2:A30000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    Sh2.Sort.SortFields.Add Key:=Range( _
        "B2:b30000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With Sh2.Sort
        .SetRange Range("A1:AN30000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
       
        End With
Sh2.Activate
 
  With ActiveSheet
      Columns("O:O").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Delete Shift:=xlToLeft
  
    End With
 
 With ActiveSheet
    .AutoFilterMode = False
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
        .AutoFilter 1, "Change Impact Ref"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With
sh.ShowAllData
Sh2.Columns("A:z").Select
    Selection.Locked = True
    Selection.FormulaHidden = True
Sh2.Columns("A:z").Select
    Selection.Locked = True
    Selection.FormulaHidden = True
'Sh2.Protect
sh.Activate
MsgBox ("Actions requiring attention are located in ""ActionReqAttn"" tab - READ ONLY")
    End Sub

 

[RoryA]

You can't change protection in a shared workbook, so that code won't work, I'm afraid.