Autofiltering a worksheet by clicking in a cell on another

[dlee70]

Hi

I have been wracking my brains and have come to a dead end. The idea is to click on a cell on Sheet 1(eg C7) and this will filter the second sheet based on the value of A7.

I have managed to do it for a single cell, and can click on any of the numbers in column C, but this filters the sheet based on the cell A7. What I need is if I click C13, it filters based on A13, or D13 =filter A13 on the second sheet.

The code I have is

------------------------------

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
If Not Intersect(Target, Range("C7:C32")) Is Nothing Then

Dim crit As String
crit = Sheets("FRONT").Range("A7").Value
Sheets("DATA").Select

With Worksheets("FRONT")
.AutoFilterMode = False
With .Range("E1:BH3000")
.AutoFilter
.AutoFilter Field:=48, Criteria1:=crit

End With
End With
End If
End If

End Sub


Any help would be appreciated.

 

[Fluff]

Hi & welcome to MrExcel.
Try

crit = target.offset(,-2).value

 

[dlee70]

Fantastic thank you. I now have the code working for (C,I,O,U). I now need (D,J,P,V) to link to a second sheet(data2) and (E,K,Q,W) to link to a third sheet (data3).

I have tried replicating the code but I can only think it has to be all done in one subroutine?

 

[Fluff]

What is your working code?

 

[dlee70]

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
If Not Intersect(Target, Range("C7:C32 , I6:I40, O6:O40, U6:U40")) Is Nothing Then

Dim crit As String
crit = Target.offset(, -2)
Sheets("DATA").Select

With Worksheets("FRONT")
.AutoFilterMode = False
With .Range("E1:BH3000")
.AutoFilter
.AutoFilter Field:=48, Criteria1:=crit

End With
End With
End If
End If

End Sub

 

[Fluff]

When posting code please use code tags, the # icon in the reply window.

How about

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Dim crit As String

   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("C7:C32 , I6:I40, O6:O40, U6:U40")) Is Nothing Then
      crit = Target.Offset(, -2)
      With Worksheets("Front")
         .AutoFilterMode = False
         With .Range("E1:BH3000")
            .AutoFilter Field:=48, Criteria1:=crit
         End With
      End With
   ElseIf Not Intersect(Target, Range("E7:E32 , K6:K40, Q6:Q40, W6:W40")) Is Nothing Then
      crit = Target.Offset(, -2)
      With Worksheets("Sheet2")
         .AutoFilterMode = False
         With .Range("E1:BH3000")
            .AutoFilter Field:=48, Criteria1:=crit
         End With
      End With
   End If
End Sub

Change ranges sheet name to suit, and the same again for the other columns