I found some help to my problem here in the archives but am unable to
relocate it to ask for clarification. I am trying to use VBA to autofilter
based on a cell ref. This works withing the same sheet but I want the
refenced cell to come from a different sheet. Is there a way to change the
code, or do I have to put the VBA script in another sheet.

This is located on a sheet called "Planning" and works when the referenced
cell is on this same sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$3" Then
Application.EnableEvents = False
If Target.Value = "" Then
ShowAllRecords
Else
Range("B4").CurrentRegion.AdvancedFilter _
Action:=xlFilterInPlace, CriteriaRange:= _
Range("K2:K3"), Unique:=False
End If
Application.EnableEvents = True
End If
End Sub

Where the Target.Address equals I3 of the Planner sheet, I want it to
reference cell B2 on the "Summary" sheet. Is there a way to do this? PS - I
know that this solution came to me by way of someone suggesting the website
contextures.

The code should go on the module for the Summary sheet, which contains
the cell you're going to change. You can modify this to include the
correct location of your criteria range, and the top left cell of the
data on the planning sheet.

'====================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsPlan As Worksheet
Set wsPlan = Worksheets("Planning")

If Target.Address = "$B$2" Then
Application.EnableEvents = False
If Target.Value = "" Then
If wsPlan.FilterMode Then
wsPlan.ShowAllData
End If
Else
wsPlan.Range("B4").CurrentRegion.AdvancedFilter _
Action:=xlFilterInPlace, CriteriaRange:= _
Range("K2:K3"), Unique:=False
End If
Application.EnableEvents = True
End If
Set wsPlan = Nothing
End Sub
'================

JICDB wrote:
I found some help to my problem here in the archives but am unable to
relocate it to ask for clarification. I am trying to use VBA to autofilter
based on a cell ref. This works withing the same sheet but I want the
refenced cell to come from a different sheet. Is there a way to change the
code, or do I have to put the VBA script in another sheet.

This is located on a sheet called "Planning" and works when the referenced
cell is on this same sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$3" Then
Application.EnableEvents = False
If Target.Value = "" Then
ShowAllRecords
Else
Range("B4").CurrentRegion.AdvancedFilter _
Action:=xlFilterInPlace, CriteriaRange:= _
Range("K2:K3"), Unique:=False
End If
Application.EnableEvents = True
End If
End Sub

Where the Target.Address equals I3 of the Planner sheet, I want it to
reference cell B2 on the "Summary" sheet. Is there a way to do this? PS - I
know that this solution came to me by way of someone suggesting the website
contextures.



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html