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

[louperrotta]

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:

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:

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!

 

[louperrotta]

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.


This sits in the ThisWorkbook module:
View attachment 114847


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

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

YES! Yep, I was definitely just entering the code in wrong, haha wow. Thank you so much man! And wow, that's so cool! I'll have to try the workbook mod! Looks awesome! Again, sorry for being such a pain, but thank you again! I really want to become well versed in Excel and VBAs, instead of just asking for help solely, last question, what would you recommend for training in the this area? I would love to learn more but sadly, I don't have the money for expensive training. What would you advise? How did you become so proficient? Probably many years of working and experience I'd imagine.

 

[StephenCrump]

No problem, I'm glad we could help.

There are plenty of free Excel/VBA resources out there. Here are a couple of links to get you started:

Recommendations for VBA learning
what is the best course to learn about excel and vba?

Many people, myself included, will tell you the best way to learn is to get in and do it. Make some mistakes. I've been occasionally corrected or embarrassed posting answers to this forum, but I have always learned something.

 

[louperrotta]

No problem, I'm glad we could help.

There are plenty of free Excel/VBA resources out there. Here are a couple of links to get you started:

Recommendations for VBA learning
what is the best course to learn about excel and vba?

Many people, myself included, will tell you the best way to learn is to get in and do it. Make some mistakes. I've been occasionally corrected or embarrassed posting answers to this forum, but I have always learned something.

Awesome! Thank you so much man, I will definitely check out those links and forums! You all are so awesome haha!

This code worked great!! So awesome! -

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:C20")) Is Nothing Then
Application.EnableEvents = False
Sheets("Sheet1").Range("A1:C20").Value = Range("A1:C20").Value
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


BUT NOW...haha sorry, Okay...haha, one last question I promise, last time...what I want to do is link individual sections of my sheets. So like...

Sheet1 A1:A20, B1:B20, C1:C20 bidirectional links to Sheet2A1:A20, B1:B20, C1:C20
Sheet1 A21:A40, B21:B40, C21:C40 links to Sheet3 A21:A40, B21:B40, C21:C40
Sheet1 A41:A60, B41:B60, C41:C60 links to Sheet 4 A41:A60, B41:B60, C41:C60...and so on and so forth.

I tried modifying the same formula and it went all wrong so how haha, I don't know if I was using the "," or ":" wrong or if I had the - If Not Intersect(Target, Range("A1:C60")) Is Nothing Then statement wrong, but I would love help on this last part, everything else is working wonderfully man!

So, do you think that I'm just using the wrong formula, or I have to use multiple formulas, and if so how would I go about that, or lastly is this where I should use the workbookchange event?

Thank you again so much for everything!

 

[louperrotta]

No problem, I'm glad we could help.

There are plenty of free Excel/VBA resources out there. Here are a couple of links to get you started:

Recommendations for VBA learning
what is the best course to learn about excel and vba?

Many people, myself included, will tell you the best way to learn is to get in and do it. Make some mistakes. I've been occasionally corrected or embarrassed posting answers to this forum, but I have always learned something.

Hey man! Nevermind my last reply, I used the workbook link and it works AMAZING!! Thank you so much!! Workbook links RULE haha, I added multiple sheets!

I'm very much going to be doing more training and check out those forums!

 

[louperrotta]

Hey man! Nevermind my last reply, I used the workbook link and it works AMAZING!! Thank you so much!! Workbook links RULE haha, I added multiple sheets!

I'm very much going to be doing more training and check out those forums!

No problem, I'm glad we could help.

There are plenty of free Excel/VBA resources out there. Here are a couple of links to get you started:

Recommendations for VBA learning
what is the best course to learn about excel and vba?

Many people, myself included, will tell you the best way to learn is to get in and do it. Make some mistakes. I've been occasionally corrected or embarrassed posting answers to this forum, but I have always learned something.

Sorry to keep bothering you but I'm still racking my brain trying to make this work with the worksheet and workbook codes -

Sheet1 A1:A20, B1:B20, C1:C20 bidirectional links to Sheet2A1:A20, B1:B20, C1:C20
Sheet1 A21:A40, B21:B40, C21:C40 links to Sheet3 A21:A40, B21:B40, C21:C40
Sheet1 A41:A60, B41:B60, C41:C60 links to Sheet 4 A41:A60, B41:B60, C41:C60...and so on and so forth.

Is this possible? To have separate linked sections in different sheets?

 

[StephenCrump]

Can you please post the code you're using (unsuccessfully)?

 

[louperrotta]

Can you please post the code you're using (unsuccessfully)?

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A20, B1:B20, C1:C20") Is Nothing Then
Application.EnableEvents = False
Sheets("Sheet1").Range("A1:A20, B1:B20, C1:C20 ").Value = Range(" A1:A20, B1:B20, C1:C20 ").Value
Sheets("Sheet2").Range(" A1:A20, B1:B20, C1:C20 ").Value = Range("A1:A20, B1:B20, C1:C20 ").Value
Sheets("Sheet3").Range("A21:A40, B21:B40, C21:C40").Value = Range("A21:A40, B21:B40, C21:C40").Value
Sheets("Sheet4").Range("A41:A60, B41:B60, C41:C60").Value = Range("A41:A60, B41:B60, C41:C60").Value
Application.EnableEvents = True
End If

End Sub

Obviously, I'm doing something very wrong haha

 

[StephenCrump]

Here's one way you could do it:

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

    Dim i As Long
    Dim rng1 As Range, rng2 As Range
    
    Application.EnableEvents = False
    
    For i = 2 To 4  'Assumes Sheet 1 <---> Sheets 2,3,4.  Extend if you wish
        Set rng1 = Worksheets("Sheet1").Range("A1:C20").Offset(20 * (i - 2))
        Set rng2 = Worksheets("Sheet" & i).Range("A1:C20").Offset(20 * (i - 2))
        If Target.Worksheet.Name = "Sheet1" Then
            If Not Intersect(Target, rng1) Is Nothing Then rng2.Value = rng1.Value
        ElseIf Target.Worksheet.Name = "Sheet" & i Then
            If Not Intersect(Target, rng2) Is Nothing Then rng1.Value = rng2.Value
        End If
    Next i
    
    Application.EnableEvents = True
    
End Sub

 

[louperrotta]

Here's one way you could do it:

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

    Dim i As Long
    Dim rng1 As Range, rng2 As Range
   
    Application.EnableEvents = False
   
    For i = 2 To 4  'Assumes Sheet 1 <---> Sheets 2,3,4.  Extend if you wish
        Set rng1 = Worksheets("Sheet1").Range("A1:C20").Offset(20 * (i - 2))
        Set rng2 = Worksheets("Sheet" & i).Range("A1:C20").Offset(20 * (i - 2))
        If Target.Worksheet.Name = "Sheet1" Then
            If Not Intersect(Target, rng1) Is Nothing Then rng2.Value = rng1.Value
        ElseIf Target.Worksheet.Name = "Sheet" & i Then
            If Not Intersect(Target, rng2) Is Nothing Then rng1.Value = rng2.Value
        End If
    Next i
   
    Application.EnableEvents = True
   
End Sub

So I put this code into the Thisworkbook and nothing happened, nothing was linked, was I supposed to put this is the workbook code or worksheet?

Something is wrong...

 

[StephenCrump]

Yes, the code belongs in the ThisWorkbook module.

If the code is not running, it could be because you have Application.EnableEvents = False, because the code you were using errored before resetting Application.EnableEvents = True.

In the Immediate Window, try typing Application.EnableEvents = True and then retry making changes.

If you can't see the Immediate Window in the VB Editor, select View/Immediate Window or type CTRL+G.