Worksheet_Change - initiate macro on another worksheet

[oms]

I am trying to get excel to automatically run a macro based off a change in
any cell in a column. I've been trying worksheet_change but have not had
much luck.

Objective:
If there is a change to any of the cells in column G of Sheet 1 (cell
contains a formula that works off other cells), I want the macro to sort data
on Sheet 2 (info that I linked from Sheet 1). I am unhiding and hiding a
column in Sheet 2 because it contains the value that I'm sorting by but I
don't want it to show in the final product.

I've attached the code below. Thanks in advance.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("G3:G110")) Is Nothing Then

Application.EnableEvents = False
Application.ScreenUpdating = False
On Error Resume Next

Sheets("Sheet 2").Select

'Unhide Column C
Columns("B").Select
Selection.EntireColumn.Hidden = False

'Sort based on Column C value
Range("C2").Select
Range("A1:C50").Sort Key1:=Range("C2"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'Hide Column C
Columns("C:C").Select
Selection.EntireColumn.Hidden = True

End If

Application.EnableEvents = True
Application.ScreenUpdating = True
On Error GoTo 0

Sheets("Sheet 1").Select

End Sub

 

[Jim Thomlinson]

Assuming that the cells in G depend only on other cells in the same worksheet
then...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler

If Not Intersect(Target, Range("G3:G110").Precedents) Is Nothing Then
Application.EnableEvents = False
Application.ScreenUpdating = False

With Sheets("Sheet2")
.Range("A1:C50").Sort Key1:=.Range("C2"), Order1:=xlDescending, _
Header:=xlYes
End With

End If
ErrorHandler:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

If there are off sheet precidents then things get ugly.

 

[oms]

Thanks Jim.

The changes you made allowed for the sort to happen. FYI, the cells in G
depend only on other cells in the same worksheet (Sheet 1).

I've been trying to make the macro hide Column C in Sheet 2 (which was the
numerical basis for the sort) after sorting. I tried placing the code within
the With and also outside of it (before End If). If I wanted to make that
happen and also adjust the sort (eg use custom list) and delete rows that
don't have a value in Column C, where would I place that code (all changes
are for Sheet 2)?

Thanks again.

 

[Jim Thomlinson]

Try this... (Untested but it should be close)

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler

If Not Intersect(Target, Range("G3:G110").Precedents) Is Nothing Then
Application.EnableEvents = False
Application.ScreenUpdating = False

With Sheets("Sheet2")
.Range("A1:C50").Sort Key1:=.Range("C2"), Order1:=xlDescending, _
Header:=xlYes, OrderCustom:=2
on error resume next
.Columns("C").SpecialCells(xlCellTypeBlanks).entirerow.Delete
.columns("C").entirecolumn.Hidden = true
on Error goto errorhandler
End With

End If
ErrorHandler:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[oms]

The update hides Column C in Sheet 2. Thanks!

However I'm having issues with deleting rows. The issue is that blank cell
values are actually an error (#VALUE!) based off the formula in Sheet 1 ("" -
""). I've tried adjusting the formula to leave "" instead of an error but
that did not work (even though the "" links over to Sheet 2). I've also
tried adjusting the code

..Columns("C").SpecialCells(xlCellTypeBlanks).entirerow.Delete

to say

..Columns("C").SpecialCells(xlCell = "").EntireRow.Delete

or even made the error spit out "a" (something completely different) and
then coded

..Columns("C").SpecialCells(xlCell = "a").entirerow.Delete

which didn't work.

I'm trying to adjust things based off my limited VB knowledge but am getting
stuck. Greatly appreciate the help.