Compare cells and force most recent change

[Looping through]

Is it possible to type some text into a cell on page 2 and force the changes
to a matching cell in sheet 1.

I.E
Sheet 2 line # 386, Note (Cell P386) = "10/1/09 - Should get order. 9/15/09
- Rep to follow up next week"
Sheet 1 line 1000, Note (Cell P1000 = "9/15/09 - Rep to follow up next week"

I want to be able to duplicate the notes in both worksheets without having
to go find the referenced line item and re-type the same info twice. My hope
is to keep everything consistant between sheets.

FYI... the line items will share a common # in Cell B.

Any help or direction will be great

 

[B Lynn B]

Assuming your notes are values of the column P cells, rather than comments on
those cells, this should work. Right click on the Sheet2 tab name, and
select "View Code" from the popup list. Paste the following lines into the
empty code screen that comes up. Whenever you make a change to column P on
sheet 2, it will change column P on sheet 1 to be the same as what you
entered, for the records with the same number in column B.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myNote As String, myID As Long
Dim FirstRow As Long, RowCount As Long, myRow As Long

If Target.Column = 16 Then
myNote = Target.Value
myID = Cells(Target.Row, 2)
FirstRow = Sheets(1).UsedRange.Row
RowCount = Sheets(1).UsedRange.Rows.Count
For myRow = FirstRow To FirstRow + RowCount
If Sheets(1).Cells(myRow, 2) = myID Then
Sheets(1).Cells(myRow, 16).Value = myNote
End If
Next myRow
End If

End Sub

 

[Jacob Skaria]

You dont need to loop..Either use Range.Find or
worksheetfunction.Match...Select the sheet tab 2 which you want to work with.
Right click the sheet tab and click on 'View Code'. This will launch VBE.
Paste the below code to the right blank portion. Get back to to workbook and
try out.

PS: The 1st sheet is referred as 'Sheet1;. Change to suit your requirement


Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet, rngTemp As Range
If Not Application.Intersect(Target, Range("P1100")) Is Nothing Then
If Target.Cells.Count = 1 And Trim(Range("B" & Target.Row)) <> "" Then
Set ws = Worksheets("Sheet1"): Application.EnableEvents = False
Set rngTemp = ws.Columns(2).Find(Range("B" & Target.Row))
If Not rngTemp Is Nothing Then ws.Range("P" & rngTemp.Row) = Target.Text
End If
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes

 

[Patrick Molloy]

this will copy every entry on sheet2 to the same cell on sheet1. There's
nothing clever here. Right-click sheet2's tab, select View Code and paste
this code..its pretty apparent what it does

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("sheet1").Range(Target.Address).Value = Target.Value
End Sub

 

[Jacob Skaria]

Hi Patrick, I know you are disturbed by yesterdays post..and I see someone
has re-posted that as a reply...Well; here OPs requirement is slightly
different from what you have suggested....and hence need a bit more
coding..Thanks

 

[Patrick Molloy]

cheers

Hi Patrick, I know you are disturbed by yesterdays post..and I see someone
has re-posted that as a reply...Well; here OPs requirement is slightly
different from what you have suggested....and hence need a bit more
coding..Thanks

 

[Looping through]

Jacob, Your the best...

You dont need to loop..Either use Range.Find or
worksheetfunction.Match...Select the sheet tab 2 which you want to work with.
Right click the sheet tab and click on 'View Code'. This will launch VBE.
Paste the below code to the right blank portion. Get back to to workbook and
try out.

PS: The 1st sheet is referred as 'Sheet1;. Change to suit your requirement


Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet, rngTemp As Range
If Not Application.Intersect(Target, Range("P1100")) Is Nothing Then
If Target.Cells.Count = 1 And Trim(Range("B" & Target.Row)) <> "" Then
Set ws = Worksheets("Sheet1"): Application.EnableEvents = False
Set rngTemp = ws.Columns(2).Find(Range("B" & Target.Row))
If Not rngTemp Is Nothing Then ws.Range("P" & rngTemp.Row) = Target.Text
End If
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes