data entry from two cells across worksheets

[sp-googling]

I'm wanting to have cells take the value of the last one updated across
worksheets. I've found the code that accomplishs this within one
worksheet, but not across two. Could anyone make ammendments to the
following code for me?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
Range("A1").Value = Range("A2").Value
ElseIf Target.Address = "$A$1" Then
Range("A2").Value = Range("A1").Value
End If
End Sub

Thanks greatly
Steve

[Ramakrishnan Rajamani]

Try this. Might work

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
Range("A1").Value = Range("A2").Value
ElseIf Target.Address = "$A$1" Then
Range("A2").Value = Range("A1").Value
End If
Sheet2.Range("A1").Value = Sheet1.Range("A1").Value
Sheet2.Range("A2").Value = Sheet1.Range("A2").Value
End Sub

Similarly for other sheets as well


"sp-googling" wrote:

> I'm wanting to have cells take the value of the last one updated across
> worksheets. I've found the code that accomplishs this within one
> worksheet, but not across two. Could anyone make ammendments to the
> following code for me?
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Address = "$A$2" Then
> Range("A1").Value = Range("A2").Value
> ElseIf Target.Address = "$A$1" Then
> Range("A2").Value = Range("A1").Value
> End If
> End Sub
>
> Thanks greatly
> Steve
>
>

[sp-googling]

I think I'm missing something...
What I tried to take from your advise resulted in this mess:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "Periferals!$A$20" Then
Monitors.Range("A20").Value = Periferals.Range("A20").Value
ElseIf Target.Address = "Monitors!$A$20" Then
Periferals.Range("A20").Value = Monitors.Range("A20").Value
End If
End Sub

which I'm putting in "ThisWorkbook" and I'm still not getting anything

[sp-googling]

sorry guys and gals, is this one tougher than I thought?

[Dave Peterson]

I'm not sure I understand what you really want...

If you make a change to A20 on Monitors or Periferals, you want that change to
be echoed onto the other worksheet?

If yes, then this worked ok for me (code is still behind the ThisWorkbook
module):

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim OtherSheetName As String
Dim myAddr As String

'only one cell at a time
If Target.Cells.Count > 1 Then
Exit Sub
End If

myAddr = "A20"

If Intersect(Target, Sh.Range(myAddr)) Is Nothing Then
Exit Sub
End If

OtherSheetName = ""
Select Case LCase(Sh.Name)
Case Is = "periferals": OtherSheetName = "monitors"
Case Is = "monitors": OtherSheetName = "periferals"
End Select

If OtherSheetName = "" Then
Exit Sub
End If

On Error GoTo errHandler:
Application.EnableEvents = False
Sh.Parent.Worksheets(OtherSheetName).Range(myAddr).Value = Target.Value

errHandler:
Application.EnableEvents = True

End Sub

By the way, I think the correct spelling for Periferals is Peripherals. If you
change the the worksheet name, remember to change the name in the code, too.


sp-googling wrote:
>
> sorry guys and gals, is this one tougher than I thought?

--

Dave Peterson

[sp-googling]

Excellent stuff Dave. What do I do if I want the two cells to be
different locations rather than just sheets?
Peripherals!A20 and monitors!B4 for example. I know I should have
asked the first time around, but you know how these things go...

btw thanks for the proof reading too :-)

[Dave Peterson]

First, I think I'd take a slightly different approach. I think I'd use just one
worksheet for input and put a formula in the other:

=monitors!b4
(in the peripherals worksheet).

If you lock that cell and protect the sheet, it should be ok.

But if you want...

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim myOtherCell As Range
Dim myPerCell As Range
Dim myMonCell As Range

'only one cell at a time
If Target.Cells.Count > 1 Then
Exit Sub
End If

Set myPerCell = Worksheets("peripherals").Range("a20")
Set myMonCell = Worksheets("monitors").Range("b4")

Set myOtherCell = Nothing
If Sh.Name = myPerCell.Parent.Name Then
If Intersect(myPerCell, Target) Is Nothing Then
Exit Sub
Else
Set myOtherCell = myMonCell
End If
ElseIf Sh.Name = myMonCell.Parent.Name Then
If Intersect(myMonCell, Target) Is Nothing Then
Exit Sub
Else
Set myOtherCell = myPerCell
End If
End If

If myOtherCell Is Nothing Then
Exit Sub
End If

On Error GoTo errHandler:
Application.EnableEvents = False
myOtherCell.Value = Target.Value

errHandler:
Application.EnableEvents = True

End Sub




sp-googling wrote:
>
> Excellent stuff Dave. What do I do if I want the two cells to be
> different locations rather than just sheets?
> Peripherals!A20 and monitors!B4 for example. I know I should have
> asked the first time around, but you know how these things go...
>
> btw thanks for the proof reading too :-)

--

Dave Peterson

[sp-googling]

top notch Dave. I'd also use formulas - since I also don't know code -
but this is on a preexisting workbook with numerous formulas
referencing the two cells and the solution you've offered will save the
integrity of the data as well as several hours of changing the
workbook. Your assistance has been very much appreciated.