Updating another sheet using an On Change event
right click on the sheet tab of Sheet1 and select view code. Put in code
like this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nMth As Long, sVal As String
Dim rng As Range, rng1 As Range
Dim res As Variant
If Target.Count 1 Then Exit Sub
If Target.Column < 2 Then Exit Sub
nMth = Month(Date)
sVal = Cells(Target.Row, 1)
With Worksheets("sheet2")
Set rng = .Range(.Cells(2, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With
res = Application.Match(sVal, rng, 0)
If Not IsError(res) Then
Set rng1 = rng(res)
rng1.Offset(0, nMth).Value = Target.Value
End If
End Sub
--
Regards,
Tom Ogilvy
"Steve Barber" wrote in message
...
I have two sheets, one which has the latest price of an item and another
which maintains a history of the price per item, I need to be able to
update
the historical price on sheet 2 in the correct column when the price is
changed on sheet1.
Example
Sheet1
Item Price
A 10
B 12
Sheet2
Item Jan Feb Mar Apr...
A 8 9 8 10
B 12 11 10 12
The values in sheet2 for April need to be updated by the changes to the
values in sheet1
The column offset is particularly perplexing me!
Anyone got any ideas
Many thanks in advance
|