Compare cells and force most recent change

L

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

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
 
J

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("P1:p100")) 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
 
P

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
 
J

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
 
P

Patrick Molloy

cheers



Jacob Skaria said:
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
 
L

Looping through

Jacob, Your the best...

Jacob Skaria said:
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("P1:p100")) 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top