worksheet_change not working

[Craig F]

I want to update some cells in a different worksheet (lets call it B)
when the value of a single cell changes in another (lets call it A). I
have inserted the code below into the worksheet_change event of
worksheet A, however when I change the value in C41 the values in
worksheet B don't change.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C41")) Is Nothing Then
Exit Sub
Else
Application.Worksheets("B").Range("B100:B108").Select
Selection.ClearContents
Application.Worksheets("B").Range("B108").Select
ActiveCell.FormulaR1C1 = "=Water_TempSales"
Application.Worksheets("B").Range("B99:B108").Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlGrowth,
Date:=xlDay, _
Trend:=True
End If

End Sub


I would appreciate some input as I am not a programmer by any stretch
of the imagination!

Thanks in advance,

Craig

 

[Barb Reinhardt]

I've cleaned up your code a bit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myWS As Worksheet

If Intersect(Target, Range("C41")) Is Nothing Then
Exit Sub
End If

Set myWS = Worksheets("B")
'if the target value is in Worksheets("B") you could use the following
'Set myWS = Target.Parent

myWS.Range("B100:B108").ClearContents
myWS.Range("B108").FormulaR1C1 = "=Water_TempSales"
myWS.Range("B99:B108").DataSeries Rowcol:=xlColumns, Type:=xlGrowth,
Date:=xlDay, _
Trend:=True

End Sub

It worked fine for me. I wonder if your events aren't enabled. In the
Immediate window, put this

Application.Enableevents = TRUE

And try it again.