I am trying to have an interactive table multiple places in my sheet to prevent my users from having to scroll repetitively through this massive sheet. every time this table is updated from any of the locations, I'd like all the tables to update. I have a (possibly useless) start:
Private Sub Worksheet_Change(ByVal Target As Range) Dim r1 As Range, r2 As Range Set r1 = Range("M205:p205") Set r2 = Range("m207:p207") If Intersect(Target, r1) Is Nothing Then Exit Sub Application.EnableEvents = False r2.Value = r1.Value Application.EnableEvents = True r1.Value = r2.Value
End Subbut this only "mirrors" cells one direction. I have no clue how to make it two-way. Ideally, I'd have 4 of these interactive tables all updating one another.
Thank you
2 Answers
Your code is actually a very good start!
I've improved it a bit to work for your scenario:
Private Sub Worksheet_Change(ByVal Target As Range) Dim r1 As Range, r2 As Range, r3 as Range, r4 as Range Dim TempRng as Range Set r1 = Range("M205:p205") Set r2 = Range("m207:p207") set r3 = Range(<your_address3>) set r4 = Range(<your_address4>) If not Intersect(Target, r1) is Nothing then set TempRng = r1 Else If not Intersect(Target, r2) is Nothing then set TempRng = r2 Else If not Intersect(Target, r3) is Nothing then set TempRng = r3 Else If not Intersect(Target, r4) is Nothing then set TempRng = r4 Else Exit Sub End If Application.Enableevents = False r1.Value = TempRng.Value r2.Value = TempRng.Value r3.Value = TempRng.Value r4.Value = TempRng.Value Application.Enableevents = True
End SubYou don't necessarily need to mirror the cells in order for users to constantly see the same cells. Excel offers three native methods for managing the "view" of spreadsheet:
Split windows (View > Split). Splits one sheet into two horizontal or two vertical sections or four quadrants of the same sheet (depends if you select a row, a column or a single cell before clicking Split).
Freeze Panes (View > Freeze Panes). Similar to Split, but top/left/top-left sections are frozen in place and can't be scrolled.
New Window (View > New Window, followed by View > Arrange all). This option creates two open "worksheet views" of the same file. Very useful to view separate sheets of the same file simultaneously. Notice the ":1" and ":2" numbers next to the filename top-centre. This indicates which view window is currently active.
All these advanced view options will save with the file and will be restored if you save, close and reopen the file.
Hope it helps! If you need more info on any of these methods, just search e.g. "how to use freeze panes in excel" in either google or youtube.
