Bi-directional (two-way) Link in Excel using cells in multiple rows and columns

L

louperrotta

New Member
Joined
Jul 30, 2024
Messages
20
Office Version
  1. 365
Platform
  1. Windows
As of right now I can only get it to work with one cell to one cell, not multiple but I am very much a layman...here is what I was using:

VBA Code: 
Private Sub Worksheet_change(ByVal Target As Range)

If Not Intersect(Target, Range("A1")) Is Nothing Then
   If Target = Range("A1") Then
      Sheets("Sheet2").Range("B1").Value = Target.Value
   End If
End If

End Sub


Private Sub Worksheet_change(ByVal Target As Range)

If Not Intersect(Target, Range("B1")) Is Nothing Then
  If Target = Range("B1") Then
    If Sheets("Sheet1").Range("A1").Value <> Target.Value Then
      Sheets("Sheet1").Range("A1").Value = Target.Value
    End If
  End If
End If

End Sub

So when I tried to use (A1:A20) & (B1:B20) it was error...what am I doing wrong? Hahaha, again, I'm a layman! Here:

VBA Code: 
Private Sub Worksheet_change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
   If Target = Range("A1:A20") Then
      Sheets("Sheet2").Range("B1:B20").Value = Target.Value
   End If
End If

End Sub


Private Sub Worksheet_change(ByVal Target As Range)

If Not Intersect(Target, Range("B1:B20")) Is Nothing Then
  If Target = Range("B1:B20") Then
    If Sheets("Sheet1").Range("A1:A20").Value <> Target.Value Then
      Sheets("Sheet1").Range("A1:A20").Value = Target.Value
    End If
  End If
End If

End Sub

Thank you so much! You are all amazing!! Hope you can help! :)
 
Last edited by a moderator:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
Welcome to the Forum!

Try:

VBA Code: 
'In Sheet1 module
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
        Application.EnableEvents = False
        Sheets("Sheet2").Range("B1:B20").Value = Range("A1:A20").Value
        Application.EnableEvents = True
    End If

End Sub
'In Sheet2 module
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("B1:B20")) Is Nothing Then
        Application.EnableEvents = False
        Sheets("Sheet1").Range("A1:A20").Value = Range("B1:B20").Value
        Application.EnableEvents = True
    End If

End Sub
 
Upvote 0
StephenCrump said:
Welcome to the Forum!

Try:

VBA Code: 
'In Sheet1 module
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
        Application.EnableEvents = False
        Sheets("Sheet2").Range("B1:B20").Value = Range("A1:A20").Value
        Application.EnableEvents = True
    End If

End Sub
'In Sheet2 module
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("B1:B20")) Is Nothing Then
        Application.EnableEvents = False
        Sheets("Sheet1").Range("A1:A20").Value = Range("B1:B20").Value
        Application.EnableEvents = True
    End If

End Sub
Click to expand...
Awesome! That worked great! Thank you so much!! What was I doing wrong? Haha, also what if I wanted to bi-directionally connect, say Sheet1 - (A1:A100, B1:B100, etc.) with Sheet2 (A1:A100, B1:B100, etc.) .... would that same VBA work? And if so how?

Also...again thank you so much! This stuff is so awesome! I def need to do VBA training haha
 
Upvote 0
StephenCrump said:
Welcome to the Forum!

Try:

VBA Code: 
'In Sheet1 module
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
        Application.EnableEvents = False
        Sheets("Sheet2").Range("B1:B20").Value = Range("A1:A20").Value
        Application.EnableEvents = True
    End If

End Sub
'In Sheet2 module
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("B1:B20")) Is Nothing Then
        Application.EnableEvents = False
        Sheets("Sheet1").Range("A1:A20").Value = Range("B1:B20").Value
        Application.EnableEvents = True
    End If

End Sub
Click to expand...
Hey man, sorry to bother you again, but I tried -

Sheet1
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A20,B1:B20")) Is Nothing Then
Application.EnableEvents = False
Sheets("Sheet2").Range("A1:A20,B1:B20").Value = Range("A1:A20,B1:B20").Value
Application.EnableEvents = True
End If

End Sub

Sheet2
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B1:B20,A1:A20")) Is Nothing Then
Application.EnableEvents = False
Sheets("Sheet1").Range("B1:B20,A1:A20").Value = Range("B1:B20,A1:A20").Value
Application.EnableEvents = True
End If

End Sub

And I can't get the code to work, I must be doing something wrong, what do you think? Again, sorry for all the questions lol!
 
Upvote 0
If the correspondence between worksheets is:

Sheet1!A1:B20 <--> Sheet2!A1:B20, e.g. Sheet1!A1 <--> Sheet2!A1, Sheet1!B10 <--> Sheet2!B10 etc, then you can use:

VBA Code: 
'In Sheet1 module
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:B20")) Is Nothing Then
        Application.EnableEvents = False
        Sheets("Sheet2").Range("A1:B20").Value = Range("A1:B20").Value
        Application.EnableEvents = True
    End If

End Sub
'In Sheet2 module
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:B20")) Is Nothing Then
        Application.EnableEvents = False
        Sheets("Sheet1").Range("A1:B20").Value = Range("A1:B20").Value
        Application.EnableEvents = True
    End If

End Sub

But based on your first post, perhaps the correspondence crosses columns, i.e.

Sheet1!A1:A20 <--> Sheet2!B1:B20, and Sheet1!B1:B20 <--> Sheet2!A1:A20?

In which case:

Code: 
'In Sheet1 module
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:B20")) Is Nothing Then
        Application.EnableEvents = False
        Sheets("Sheet2").Range("A1:A20").Value = Range("B1:B20").Value
        Sheets("Sheet2").Range("B1:B20").Value = Range("A1:A20").Value
        Application.EnableEvents = True
    End If

End Sub
'In Sheet2 module
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:B20")) Is Nothing Then
        Application.EnableEvents = False
        Sheets("Sheet1").Range("A1:A20").Value = Range("B1:B20").Value
        Sheets("Sheet1").Range("B1:B20").Value = Range("A1:A20").Value
        Application.EnableEvents = True
    End If

End Sub

The code adopts a pragmatic approach. We could add code to test which cells have been changed, and then copy only those values to the other worksheet.

But instead, the code says, have changes been made anywhere in A1:B20? If so, copy all values to the other worksheet.

For similar reasons, I'd change your code in Post#1

Code: 
'From
If Sheets("Sheet1").Range("A1").Value <> Target.Value Then
    Sheets("Sheet1").Range("A1").Value = Target.Value
End If

'To
Sheets("Sheet1").Range("A1").Value = Target.Value
 
Upvote 0
StephenCrump said:
If the correspondence between worksheets is:

Sheet1!A1:B20 <--> Sheet2!A1:B20, e.g. Sheet1!A1 <--> Sheet2!A1, Sheet1!B10 <--> Sheet2!B10 etc, then you can use:

VBA Code: 
'In Sheet1 module
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:B20")) Is Nothing Then
        Application.EnableEvents = False
        Sheets("Sheet2").Range("A1:B20").Value = Range("A1:B20").Value
        Application.EnableEvents = True
    End If

End Sub
'In Sheet2 module
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:B20")) Is Nothing Then
        Application.EnableEvents = False
        Sheets("Sheet1").Range("A1:B20").Value = Range("A1:B20").Value
        Application.EnableEvents = True
    End If

End Sub

But based on your first post, perhaps the correspondence crosses columns, i.e.

Sheet1!A1:A20 <--> Sheet2!B1:B20, and Sheet1!B1:B20 <--> Sheet2!A1:A20?

In which case:

Code: 
'In Sheet1 module
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:B20")) Is Nothing Then
        Application.EnableEvents = False
        Sheets("Sheet2").Range("A1:A20").Value = Range("B1:B20").Value
        Sheets("Sheet2").Range("B1:B20").Value = Range("A1:A20").Value
        Application.EnableEvents = True
    End If

End Sub
'In Sheet2 module
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:B20")) Is Nothing Then
        Application.EnableEvents = False
        Sheets("Sheet1").Range("A1:A20").Value = Range("B1:B20").Value
        Sheets("Sheet1").Range("B1:B20").Value = Range("A1:A20").Value
        Application.EnableEvents = True
    End If

End Sub

The code adopts a pragmatic approach. We could add code to test which cells have been changed, and then copy only those values to the other worksheet.

But instead, the code says, have changes been made anywhere in A1:B20? If so, copy all values to the other worksheet.

For similar reasons, I'd change your code in Post#1

Code: 
'From
If Sheets("Sheet1").Range("A1").Value <> Target.Value Then
    Sheets("Sheet1").Range("A1").Value = Target.Value
End If

'To
Sheets("Sheet1").Range("A1").Value = Target.Value
Click to expand...
Hey man! Yeah, I get that! Awesome!

This VBA worked perfect! That's what I was trying to do is have them "mirror" each other but in a two-way link! Dude you're awesome!

So yeah this VBA is perfect -

'In Sheet1 module
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:B20")) Is Nothing Then
Application.EnableEvents = False
Sheets("Sheet2").Range("A1:B20").Value = Range("A1:B20").Value
Application.EnableEvents = True
End If

End Sub
'In Sheet2 module
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:B20")) Is Nothing Then
Application.EnableEvents = False
Sheets("Sheet1").Range("A1:B20").Value = Range("A1:B20").Value
Application.EnableEvents = True
End If

End Sub

The last thing, my last question, haha, is how could I add columns to the code, if that makes sense so...Sheet1 column A links to Sheet 2 Column A, Sheet1 column B links to Sheet 2 Column B, Sheet1 column C links to Sheet 2 Column C...and so on...you know? Do I just repeat the code? AND what if I wanted to though another sheet in the mix? Sheet1 column A links to Sheet 2 Column A, links to Sheet 3 Column A?? Is that possible?

Sorry for all the questions man, I just find this so fascinating, I don't want to waste too much of your time but how does the VBA code you made work, and what was it I was doing wrong? If you don't mind!

Thank you so much for everything!!! :)
 
Upvote 0
If the columns are contiguous, you can expand the range like this:

VBA Code: 
'In Sheet1 module
'Do similar in Sheet2 and Sheet3
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:C20")) Is Nothing Then
        Application.EnableEvents = False
        Sheets("Sheet2").Range("A1:C20").Value = Range("A1:C20").Value
        Sheets("Sheet3").Range("A1:C20").Value = Range("A1:C20").Value
        Application.EnableEvents = True
    End If

End Sub

Depending on how complicated this gets, it may be appropriate to use the Workbook_SheetChange event instead, and have all your code in the one place:

Code: 
'ThisWorbook module
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    'Put code here to test worksheet (Sh.Name) and the range changed (Target), and update other sheets accordingly

End Sub
 
Upvote 0
StephenCrump said:
If the columns are contiguous, you can expand the range like this:

VBA Code: 
'In Sheet1 module
'Do similar in Sheet2 and Sheet3
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:C20")) Is Nothing Then
        Application.EnableEvents = False
        Sheets("Sheet2").Range("A1:C20").Value = Range("A1:C20").Value
        Sheets("Sheet3").Range("A1:C20").Value = Range("A1:C20").Value
        Application.EnableEvents = True
    End If

End Sub

Depending on how complicated this gets, it may be appropriate to use the Workbook_SheetChange event instead, and have all your code in the one place:

Code: 
'ThisWorbook module
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    'Put code here to test worksheet (Sh.Name) and the range changed (Target), and update other sheets accordingly

End Sub
Click to expand...
Okay so in that regard, I did the code to do 500 cells in each column (A, B, C) using your code and it looked like this -

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:C500")) Is Nothing Then
Application.EnableEvents = False
Sheets("Sheet2").Range("A1:C500").Value = Range("A1:C500").Value
Sheets("Sheet3").Range("A1:C500").Value = Range("A1:C500").Value
Application.EnableEvents = True
End If

End Sub

BUT something WEIRD is happening though so, for some reason, and this could just be my own mess up, when I go to type data in any of those cells in Sheet1 the connection works and shows up in both Sheet2 and Sheet3 BUT when I type data into a cell in either Sheet2 or Sheet3 the data shows up in those two sheets (Sheet2 & Sheet3) but not in Sheet1, which is so weird, it's like there is a connection break or something...what do you think is going on?

Hope you're having a good day man, thanks for everything, you rule!

Also, I have no clue what a Workbook_SheetChange event is, sorry man, again layman to the nth degree haha, what is it?
 
Upvote 0
StephenCrump said:
If the columns are contiguous, you can expand the range like this:

VBA Code: 
'In Sheet1 module
'Do similar in Sheet2 and Sheet3
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:C20")) Is Nothing Then
        Application.EnableEvents = False
        Sheets("Sheet2").Range("A1:C20").Value = Range("A1:C20").Value
        Sheets("Sheet3").Range("A1:C20").Value = Range("A1:C20").Value
        Application.EnableEvents = True
    End If

End Sub

Depending on how complicated this gets, it may be appropriate to use the Workbook_SheetChange event instead, and have all your code in the one place:

Code: 
'ThisWorbook module
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    'Put code here to test worksheet (Sh.Name) and the range changed (Target), and update other sheets accordingly

End Sub
Click to expand...
Hey man, I also wanted to say sorry for the second post, I should've read the rules, my bad. I feel like a real jerk, I didn't mean to duplicate the question. Very sorry.
 
Upvote 0
louperrotta said:
BUT something WEIRD is happening ....
Click to expand...
It shouldn't be a mystery. Look closely at your code for Sheet2 and Sheet3, and you'll probably find you haven't changed the sheet references? Your code in the Sheet1 module refers to Sheet2 and Sheet3, so if you're copying to the Sheet2 module, for example, you'll need to change these references to Sheet1 and Sheet3.

Post your code if you can't identify the problem.

louperrotta said:
I have no clue what a Workbook_SheetChange event is
Click to expand...
This sits in the ThisWorkbook module:

1722562136955.png


1722562121156.png

Instead of having three Worksheet_Change subs, you could have just one Sub:

VBA Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim r As String
  
    r = "A1:C500"
  
    If (Sh.Name = "Sheet1" Or Sh.Name = "Sheet2" Or Sh.Name = "Sheet3") And _
    Not Intersect(Sh.Range(r), Target) Is Nothing Then
        Application.EnableEvents = False
        Sheets("Sheet1").Range(r).Value = Sh.Range(r).Value
        Sheets("Sheet2").Range(r).Value = Sh.Range(r).Value
        Sheets("Sheet3").Range(r).Value = Sh.Range(r).Value
        Application.EnableEvents = True
    End If

End Sub
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

L
  • Locked
  • Question Question
*Updated Question*...on creating a Bi-directional (two-way) Link in Excel that can link multiple rows and columns?? (You all are amazing!)
Replies
2
Views
410
Joe4
Joe4
S
  • Solved
Help with Code for Ambiguous Error
Replies
3
Views
76
Fuji
F
E
  • Question Question
VBA - If Cell IsDate and Another Column is a formula then make Cell a hyperlink
2
Replies
10
Views
205
Alex Blakenburg
A
E
  • Locked
  • Question Question
VBA - If Cell IsDate and Another Column is a formula then make Cell a hyperlink
Replies
2
Views
102
Fluff
Fluff
M
  • Solved
Trying to apply 2 Worksheet Changes
Replies
1
Views
235
Joe4
Joe4

Forum statistics

Threads
1,224,814
Messages
6,181,130
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top