# Advanced filter from another workbook

#### HughT

##### Board Regular
Fluff very kindly posted the following code to enable the automation of advanced filtering from one worksheet to another in the same workbook for variable criteria without having to reinput the criteria each time. But how would this be done from another workbook? The data is in a workbook called Project Log which has many worksheets containing sensitive information. I want to create a separate workbook for general users (called Project Lookup) which would enable them to run the advanced filter from that location without any risk of accessing the sensitive data. The worksheet for this would be called Project Data.

Is this possible?

Many thanks

HT

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Address(0, 0) = "C4" Then
With Sheets("Pcode").Range("A4:D200")
.AutoFilter
End With
End If
End Sub

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### John_w

##### MrExcel MVP
The simplest solution is to open the Project Log workbook momentarily and do the advanced filter on the required sheet (here named "Data Sheet") in that workbook:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim DataWorkbook As Workbook

If Target.CountLarge > 1 Then Exit Sub

If Target.Address(0, 0) = "C4" Then
Application.ScreenUpdating = False
Set DataWorkbook = Workbooks.Open("C:\folder\path\to\Project Log.xlsx") 'CHANGE PATH
With DataWorkbook.Worksheets("Data Sheet").Range("A4:D200") 'CHANGE SHEET NAME
.AutoFilter
End With
DataWorkbook.Close False
Application.ScreenUpdating = True
End If

End Sub
If you don't want to open the Project Log workbook - and I suppose there is a risk that it might remain open, for whatever reason - then a better solution would be an SQL query of the Project Log workbook using ADODB.

#### HughT

##### Board Regular
Very many thanks for this. Apologies, I should have mentioned that both workbooks will be held in Sharepoint. Will this make a difference, or do I just copy the Sharepoint path into where you have the reference to C drive?

Thank you

HT

#### John_w

##### MrExcel MVP
Yes, try using the Sharepoint path, although I've never used Sharepoint so don't know if it will work.

As a test, have both files on your local drive and see if the time taken to open the Project Log workbook is acceptable - it might not be if it's a large file with many worksheets. In that case we could try the ADODB approach.