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.

 

[isabelarodriigs]

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?

It does not show which line the error is coming from. I saved the document in the format you said it

 

[kevin9999]

The problem is you don't have a sheet called "mydata". Is it the sheet you've called "L1897128" that you want to filter? If so, change the following line of code to be:

With Sheets("L1897128")

 

[isabelarodriigs]

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

I renamed the sheet in the third tab "mydata", so when you press CTRL A, thats what pop up. but the bottom of my third tab, the name is L1897128

 

[isabelarodriigs]

The problem is you don't have a sheet called "mydata". Is it the sheet you've called "L1897128" that you want to filter? If so, change the following line of code to be:

With Sheets("L1897128")

YOU ARE AWESOME!!!! THANK YOU!!!!!

 

[kevin9999]

YOU ARE AWESOME!!!! THANK YOU!!!!!

It looks like you've got it working now? Happy to help and thanks for the feedback