Mirror cells across worksheets without hardcoding (much)

I've found many solutions to mirror multiple cells across multiple worksheets in this forum, but they're so dauntingly hardcoded that they look like the equivalent of

Please Login or Register  to view this content.

And so on, quickly becoming unmanageable and hell to mantain, what if there're 20 worksheets and 200 mirrored cells? Good luck moving a single row/column, it WILL break everything.
I'm not familiar with VBA, but is it possible to create the Python equivalent of:

Please Login or Register  to view this content.

This is visually much simpler to manage and mantain, each column represents the cells that are to be mirrored with their respective worksheet on the left

Please Login or Register  to view this content.

I can add new rows and columns without breaking anything, only one script that handles it all:

Please Login or Register  to view this content.

Here's a working Python 3 script you can try (I didn't implement all the logic, but it's to give you an idea)

Please Login or Register  to view this content.

This is the output after running it

Please Login or Register  to view this content.

Any chance something similar to this can be implemented in VBA?
Thank you very much!

Thank you for taking time in writing this code, I really appreciate it! I'm trying to do the same thing as the document attached here, but with a more manageable format, if I used your example, I guess it should look like this

Please Login or Register  to view this content.

But with the ability to add/remove new rows/columns and reflect these changes, such as.

Please Login or Register  to view this content.

Sadly I have zero VBA experience and don't know how implement this logic.
P.S.= Using AT, otherwise it detects my post as a url and blocks me.

According to your attachment if you really want to play with arrays the event revamped for Sheet1 moodule :

PHP Code: 

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim V
        If Target.CountLarge > 1 Then Exit Sub
    With Application
        V = .Match(Target.Address(0, 0), [{"B1","B4"}], 0)
    If IsNumeric(V) Then
       .EnableEvents = False
    For Each V In Array([{"Sheet2!B2","Sheet3!C5"}], [{"Sheet2!B3","Sheet3!C6"}])(V - 1)
        Range(V).Value2 = Target.Value2
    Next
        .EnableEvents = True
    End If
    End With
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

According to your post #6 sample 'Groceries' is useless within the array as the event must be in the Groceries worksheet module :

PHP Code: 

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim V, C&, R&
        If Target.CountLarge > 1 Then Exit Sub
        V = Array([{"",         "A1","A2","A3","A4"}], _
                  [{"Traveling","B1","B2","B3","B4"}], _
                  [{"Transport","C1","C2","C3","C4"}], _
                  [{"Treatment","D1",""  ,"D3",""}])
    For C = 2 To UBound(V(0))
        If Target.Address(0, 0) = V(0)(C) Then
            Application.EnableEvents = False
        For R = 1 To UBound(V)
            If V(R)(C) > "" Then Sheets(V(R)(1)).Range(V(R)(C)).Value2 = Target.Value2
        Next
            Application.EnableEvents = True
            Exit For
        End If
    Next
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

According to Excel basics another way to build the 'Groceries' array :

PHP Code: 

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim V, C&, R&
        If Target.CountLarge > 1 Then Exit Sub
        V = Evaluate("{"""",         ""A1"",""A2"",""A3"",""A4"";" & _
                      """Traveling"",""B1"",""B2"",""B3"",""B4"";" & _
                      """Transport"",""C1"",""C2"",""C3"",""C4"";" & _
                      """Treatment"",""D1"",""""  ,""D3"",""""}")
    For C = 2 To UBound(V, 2)
        If Target.Address(0, 0) = V(1, C) Then
            Application.EnableEvents = False
        For R = 2 To UBound(V)
            If V(R, C) > "" Then Sheets(V(R, 1)).Range(V(R, C)).Value2 = Target.Value2
        Next
            Application.EnableEvents = True
            Exit For
        End If
    Next
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Originally Posted by AndroYD

Actually, if anyone can think of a better strategy to mirror multiple cells across multiple worksheets, I'm open to it.

Same strategy but using a 'Settings' worksheet rather than hardcoding anything …

Woah! Thank you so much for your help! This is hands down the best mirroring solution you can find here in this forum by far imo! It's still a bit hardcodey, in the sense you have to modify the code for every sheet on the first array if you want to mirror both ways (ie. Sheet X <-> Sheet Y rather than Sheet X -> Sheet Y) but it's still a huge improvement compared to any other mirroring solutions available in this forum that are impossible to manage on a large scale. I have no experience in VBA so I can't tell if there're any hard limitations with this language, but in Python it's completely possible to keep all the mirroring rules in one place (script/sheet/module) that acts like an information box, while all the other sheets call this information box to collect their mirroring rules. So when I want to make a modification, there's just one script to change, I don't need to edit every script for every sheet. Makes sense?

Originally Posted by Marc L

Same strategy but using a 'Settings' worksheet rather than hardcoding anything …

I have thought of using a 'Settings' worksheet, that wouldn't even require any VBA knowledge, but unfortunately it's very inconvenient when the choice of data is based on what is directly observable on the sheets in real-time, lots of switching.

Maybe but no needs to amend the code and best you do not ever need any event procedure within each worksheet module
but only a single event located in the Thisworkbook module …

Originally Posted by AndroYD

but in Python it's completely possible to keep all the mirroring rules in one place

According to Python :

• pyxll
  
  
• xlwings …

Woah! This is absolutely perfect, makes the job so much easier to handle! I think you've won the crown for best mirroring solution in this forum, this is by far the smarter and most convenient way to handle it IMO, so simple yet effective!

The problem of using Python to handle Excel is portability, VBA code is embedded and natively working by default as-is without needing any extras and licenses to buy.