Worksheet_Change Event - to perform task on different worksheet

[npvrader]

Can I program a worksheet_change event that fires when a cell in worksheet Input changes and performs an operation (e.g., sorts) a target worksheet? In other words can you change the worksheet focus from the changed sheet to the sheet on which you want to perform an action on?

I am coding a simple project in Excel VBA that uses a single workbook with multiple worksheets. The user inputs daily information in the Input sheet and when finished runs a macro to update the worksheet related to that particular product (e.g., Corn, Soybeans, Cotton, .....). On the input sheet, the user selects the product from a validation drop down list. When that cell changes, it signifies the product that the user wants to report on and it preps the product worksheet- by sorting it.

I have placed the following code in the Input worksheets module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim keyCells As Range

Set keyCells = Range("E2:E3")

If Not Application.Intersect(keyCells, Range(Target.Address)) Is Nothing Then

    Set mWbk = ActiveWorkbook
    Set iSht = ActiveSheet
    iSht.Name = "Input"
    tSheet = mWbk.Sheets("Input").Range("E3").Value

    mWbk.Worksheets(tSheet).Activate
   
    Range("A1").CurrentRegion.Sort key1:=Range("A1"), order1:=xlAscending, Header:=xlYes
    
End If
End Sub

When the keycells are changed this fires and runs until tSheet is activated. I get a 1004 error on the Range.Sort method. FYI - it does activate the tSheet.

If anyone has insight into this would appreciate the help.

 

[StephenCrump]

You just need to qualify your references to Range("A1") with a sheet name. Try

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim tSheet As Worksheet
    Dim keyCells As Range
    Dim sSheetName As String
    
    Set keyCells = Range("E2:E3")
    
    If Intersect(keyCells, Target) Is Nothing Then Exit Sub
    
    sSheetName = keyCells(2)
    On Error GoTo SheetError:
    Set tSheet = Worksheets(sSheetName)
    On Error GoTo 0
    tSheet.Range("A1").CurrentRegion.Sort Key1:=tSheet.Range("A1"), Order1:=xlAscending, Header:=xlYes
    MsgBox "Sheet: " & sSheetName & " sorted!"
    Exit Sub

SheetError:
    MsgBox sSheetName & " is not a valid worksheet name!"
    
End Sub

 

[npvrader]

Thanks for the direction, you were right.

I thought simply activating the worksheet as below would provide a default reference but that is not the case or my activation didn't work as I had planned it.

tSheet = mWbk.Sheets("Input").Range("E3").Value
mWbk.Worksheets(tSheet).Activate

What I ended up using was :
Set tSheet = Workheets(tSheetName)

and I revised the line as Stephen Crump suggested:

tSheet.Range("A1").CurrentRegion.Sort Key1:=tSheet.Range("A1"), Order1:=xlAscending, Header:=xlYes

Much cleaner and I didn't have to activate.

Again, thank your Stephen!