Advanced filter from another workbook

[HughT]

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")
.AdvancedFilter xlFilterCopy, Me.Range("C3:C4"), Me.Range("A6:D6"), False
.AutoFilter
End With
End If
End Sub

 

[John_w]

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:

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
            .AdvancedFilter xlFilterCopy, Me.Range("C3:C4"), Me.Range("A6:D6"), False
            .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]

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]

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.