I want to connect three cells across three worksheets, so that when any of these three cells are updated, the update is reflected in the other two cells:
Sheet 1 B10
Sheet 2 B11
Sheet 3 B12
I was able to use this macro for two-way linking but wasn't able to find something that worked for me for three-way linking:
Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. If you use them you don't need all of those INDENT tags. I have added them for you this time because you are a new member. --6StringJazzer
I'm using Excel for Microsoft 365.
Last edited by 6StringJazzer; 02-24-2022 at 03:04 PM.
To avoid a never ending story like in previous post you must first erase the VBA event Worksheet_Change in each worksheet module.
Then paste this workbook event to the ThisWorkbook module :
PHP Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim V, W, X, S& V = [{"Sheet1","Sheet2","Sheet3"}] W = Application.Match(Sh.Name, V, 0): If IsError(W) Then Exit Sub X = [{"B10","B11","B12"}]: If Target.Address(False, False) <> X(W) Then Exit Sub Application.EnableEvents = False For S = 1 To UBound(V) If S <> W Then Sheets(V(S)).Range(X(S)).Value2 = Target.Value2 Next Application.EnableEvents = True End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Can I ask how you would modify the code to apply to multiple cell links across sheets (e.g. B10 = B11 = B12, but also C10 = C11 = C12, etc.)? I made a few educated guesses without any success.
Can I ask how you would modify the code to apply to multiple cell links across sheets (e.g. B10 = B11 = B12, but also C10 = C11 = C12, etc.)? I made a few educated guesses without any success.
Yes 'cause according to the initial post it was not designed for multiple cells by worksheet …
As it depends on how many sheets, how many cells … the more the harder to hardcode and to maintain by yourself !
An easy way is to not hardcode anything but to just use a 'Settings' - or 'Parameters' whatever - worksheet …
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim V, L, C, R& V = [{"Sheet1","B10","C10";"Sheet2","B11","C11";"Sheet3","B12","C12"}] With Application L = .Match(Sh.Name, .Index(V, 0, 1), 0): If IsError(L) Then Exit Sub C = .Match(Target.Address(False, False), .Index(V, L, 0), 0): If IsError(C) Then Exit Sub .EnableEvents = False For R = 1 To UBound(V) If R <> L Then If V(R, C) > "" Then Sheets(V(R, 1)).Range(V(R, C)).Value2 = Target.Value2 Next .EnableEvents = True End With End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Bookmarks