Open another Workbook and apply filter

[Martin_H]

Hi all,

whit this code I would like to open another Excel Worbook (which is locked and shared) if not already opened and apply the filter on Worksheet named "1" to the cell A1 (cell A1 is a header, but it is not a table just a range). So basically after filter is done I want to see only numbers 2, if there are any, under the cell A1.

Sub ApplyFilter()

    On Error Resume Next: Err.Clear: Dim wb As Workbook
    Set wb = Workbooks("New Microsoft Excel Worksheet.xlsm"): wb.Activate
    If Err.Number > 0 Then Set wb = Workbooks.Open(Filename:="C:\Users\DLBV9NJ\Desktop\New Microsoft Excel Worksheet.xlsm", ReadOnly:=False)
    If Not wb Is Nothing Then wb.Worksheets("1").Activate
Sheets("1").Visible = True

    With Worksheets("1")
.Activate
.Range("A1").AutoFilter field:=1, Criteria1:="2"
    End With

End Sub

Code works ok, it opens the Workbook I want but will not filter anything meanwhile filtering on that Shared/Locked Workbook is allowed. I have locked it before with the code below (manual filtering on that Shared/Locked Workbook is working).

Sub Protect1()

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowSorting:=True, AllowFiltering:=True, UserInterfaceOnly:=True

End Sub

Please help me with this one.


Thank you!

 

[davesexcel]

Remove the error traps and see if you are getting an error.

 

[Martin_H]

After removing error traps I am getting Run-time error 1004. Application-defined or object-defined error.

 

[davesexcel]

Does a line in the code get highlited?

I assume you are trying to set the workbook, before it is opened.

 

[Martin_H]

Nothing got highlighted at all.

The code works perfectly until I set the Workbook to "Shared" state.

 

[davesexcel]

Oh, darn, I missed the "shared" part. Shared workbooks have limitations.