How to filter data in one sheet just by clicking cell content in a different sheet in Excel?

[isabelarodriigs]

I would like to to click on on my sheet 1 in excel and automatically filter on my third sheet. I using this code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("A:C")) Is Nothing Then Exit Sub
With Sheets("mydata").Cells(7, 1).CurrentRegion
.AutoFilter 1, Target.Offset(, -1)
.AutoFilter 2, "Completed"
End With
End Sub

but when i double click, i get to run-time error '9'. Subscript out of range. Then it highlights this part With Sheets("mydata").Cells(7, 1).CurrentRegion
saying that table does not exist, but it does.

this is my sheet 1:

DIVISION ID	DIVISION NAME	DEPARTMENT ID	Updated to Loma Linda Department ID/GL	CURRENT IM DEPARTMENT NAME
9423	RECORDS MANAGEMENT	20004		20004

my second sheet:

CUSTOMER ID	CUSTOMER NAME	DISTRICT ID	DISTRICT NAME	DIVISION ID	DIVISION NAME	DEPARTMENT ID
LLUHS	LOMA LINDA UNIVERSITY HEALTH SYSTEM	LA	LOS ANGELES, CA	LLUMC	LOMA LINDA UNIVERSITY MEDICAL CTR	8154

I want to click on sheet 1, column C, and filter automatically sheet2, column G.

 

[kevin9999]

Hi and welcome to the forum

There's a couple of issues with your code, but before I suggest an alternative, could you confirm that the header row in the sheet mydata is row 1, that you want to filter column G by the Department ID you double clicked on from column C in your sheet 1, and also - where is "Completed" located in mydata?

 

[isabelarodriigs]

Hi and welcome to the forum

There's a couple of issues with your code, but before I suggest an alternative, could you confirm that the header row in the sheet mydata is row 1, that you want to filter column G by the Department ID you double clicked on from column C in your sheet 1, and also - where is "Completed" located in mydata?

Hi Kevin, Thank you for helping

so the name of my first sheet is "DivandDept", and the name of my second sheet (which is the 3rd tab) is "mydata". Yes! In both sheets the header is in row 1. I took a screenshot so you can see how it looks like, the 3rd picture is how I named each tab, I don't know if that makes a difference.

 

[kevin9999]

Thank you for this, but also: where is "Completed" located ( which column) and what is its significance?

 

[isabelarodriigs]

Thank you for this, but also: where is "Completed" located ( which column) and what is its significance?

I copied this code from a website, so that's where it is from. I thought "completed" meant the code was complete

 

[kevin9999]

No worries, leave it with me.

 

[kevin9999]

Please try the following on a copy of your workbook. Right-click on the tab of your "DivandDept" sheet, select View Code, and put the following in the window that appears on the right of screen. Save the file as macro-enabled or binary format. When you double click on a cell in column C of your DivandDept sheet, your mydata sheet should be filtered on column G with whatever the value was in that cell.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, cancel As Boolean)
    On Error GoTo Escape
    Application.EnableEvents = False
    If Len(Target.Value) > 0 And Target.Column = 3 And Target.Row > 1 Then
        cancel = True
        With Sheets("mydata")
            If .AutoFilterMode Then .AutoFilter.ShowAllData
            With .Range("A1").CurrentRegion
                .AutoFilter 7, Target.Value
            End With
        End With
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

 

[isabelarodriigs]

Please try the following on a copy of your workbook. Right-click on the tab of your "DivandDept" sheet, select View Code, and put the following in the window that appears on the right of screen. Save the file as macro-enabled or binary format. When you double click on a cell in column C of your DivandDept sheet, your mydata sheet should be filtered on column G with whatever the value was in that cell.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, cancel As Boolean)
    On Error GoTo Escape
    Application.EnableEvents = False
    If Len(Target.Value) > 0 And Target.Column = 3 And Target.Row > 1 Then
        cancel = True
        With Sheets("mydata")
            If .AutoFilterMode Then .AutoFilter.ShowAllData
            With .Range("A1").CurrentRegion
                .AutoFilter 7, Target.Value
            End With
        End With
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

How do I save as as macro-enabled or binary format?

When I saved the code and tried to run it a message came out "Error 9: Subscript out of range"

 

[kevin9999]

File/Save As/ and scroll to select either of those formats. I assume you tried to run it merely by double clicking on a cell on the DivandDep sheet? What line of code did the error occur on?

 

[kevin9999]

Looking at your images, you don't have a sheet called "mydata"?