I have a workbook with two worksheets, each one with a table and I'm trying
to get a macro to run that filters the the second worksheet table with the
value of the the cell selected in column A of the first worksheet. After
getting some hints here on this forum I came up with:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Windows("Inventory Items.xlsm:1").Activate

If ActiveCell.Column = 1 Then
Dim ItemNum As String
ItemNum = "=" & ActiveCell.Text
Windows("Inventory Items.xlsm:2").Activate
ActiveSheet.ListObjects("Table_Default__ipurch").R ange.AutoFilter
Field:=1, _
Criteria1:=ItemNum, Operator:=xlAnd
Windows("Inventory Items.xlsm:2").Activate
End If
End Sub

but it doesn't work automatically when I select another cell (in the first
column)even though it runs perfectly when I run it manually with the first
line changed to:

Sub FilterPurchases()

How does one get it to run automatically when the selection changes?

Did you put it in the worksheet code module? The worksheet_selctionchange
will not work from the standard code Module1. You must right click the name
tab of the active sheet, then click View Code from the drop down menu. That
opens the sheet code module. Paste your code there and then try it.

"Bob Arnett" wrote:

I have a workbook with two worksheets, each one with a table and I'm trying
to get a macro to run that filters the the second worksheet table with the
value of the the cell selected in column A of the first worksheet. After
getting some hints here on this forum I came up with:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Windows("Inventory Items.xlsm:1").Activate

If ActiveCell.Column = 1 Then
Dim ItemNum As String
ItemNum = "=" & ActiveCell.Text
Windows("Inventory Items.xlsm:2").Activate
ActiveSheet.ListObjects("Table_Default__ipurch").R ange.AutoFilter
Field:=1, _
Criteria1:=ItemNum, Operator:=xlAnd
Windows("Inventory Items.xlsm:2").Activate
End If
End Sub

but it doesn't work automatically when I select another cell (in the first
column)even though it runs perfectly when I run it manually with the first
line changed to:

Sub FilterPurchases()

How does one get it to run automatically when the selection changes?

Excellent. I was just in the wrong place.

"JLGWhiz" wrote:

Did you put it in the worksheet code module? The worksheet_selctionchange
will not work from the standard code Module1. You must right click the name
tab of the active sheet, then click View Code from the drop down menu. That
opens the sheet code module. Paste your code there and then try it.

"Bob Arnett" wrote:

I have a workbook with two worksheets, each one with a table and I'm trying
to get a macro to run that filters the the second worksheet table with the
value of the the cell selected in column A of the first worksheet. After
getting some hints here on this forum I came up with:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Windows("Inventory Items.xlsm:1").Activate

If ActiveCell.Column = 1 Then
Dim ItemNum As String
ItemNum = "=" & ActiveCell.Text
Windows("Inventory Items.xlsm:2").Activate
ActiveSheet.ListObjects("Table_Default__ipurch").R ange.AutoFilter
Field:=1, _
Criteria1:=ItemNum, Operator:=xlAnd
Windows("Inventory Items.xlsm:2").Activate
End If
End Sub

but it doesn't work automatically when I select another cell (in the first
column)even though it runs perfectly when I run it manually with the first
line changed to:

Sub FilterPurchases()

How does one get it to run automatically when the selection changes?