Ok – so this guy give me the one tip that mattered. Application.Windows(1) is the active window. ALSO – you can change the caption of the window. IF you have a workbook and need to show 2 or more worksheets in as many windows – then it renames the sheets and adds :1 and :2 – which DO NOT CHANGE. But the windows(1) is NOT reliably window(1) the next time you interact with the windows.
So if you want sheet1’s window to return sheet1 and sheet2’s window to interact with sheet2 – then here is some code to help one understand what is going on. What I am going to do is change the :1 to :DELTA and :2 to :SOURCE and ensure that those windows show the delta and source worksheets respectively
Sub Test_WindowIndexes() ' Here is the guy to inspired me to fiddle and get some understanding. It is mental but ' there is some hope is his bottom line. https://www.engram9.info/excel-vba-programming-3/the-workbook-and-window-objects.html ' before you run this - make a workbook with 2 worksheets ' rename them something memorable ' Next - simply make a new window . Use hte View Ribbon->New Window button ' or Set mywb = workbook.newWindow command here in VBA (in future) Debug.Print "*********************new" ' just a line so you can see what 'is going on between running this and loooking in the immediate window Debug.Print "activesheet: " & ActiveSheet.Parent.Windows(1).Caption ActiveSheet.Parent.Windows(1).Caption = VBA.Replace(ActiveSheet.Parent.Windows(1).Caption, ":1", ":DELTA") Dim vvar As Variant Dim w As Window ' lets set aside some static instances of the windows as they are right now Dim winds As New Collection winds.Add ActiveSheet.Parent.Windows(1) ' the active screen winds.Add ActiveSheet.Parent.Windows(2) ' some random worksheet ' lets cycle through the worksheets. ' Set a breakpoint AFTER the Next - play with the windows and re-run this loop. ' the Application.windows collection CHANGES INDEXES - how unstable! For Each vvar In ActiveSheet.Parent.Windows Set w = vvar Debug.Print "collection: " & w.Caption & " --> " & w.ActiveSheet.Name Next ' now let VBA muck with the active window winds(1).activate Debug.Print ".windows(1):" & Application.Windows(1).Caption Debug.Print "w1:" & winds(1).Caption Debug.Print "w2:" & winds(2).Caption Debug.Print " ... now switch to winds(2) as active" winds(2).activate Debug.Print ".windows(1):" & Application.Windows(1).Caption Debug.Print "w1:" & winds(1).Caption Debug.Print "w2:" & winds(2).Caption ' wind(1) and winds(2) are finally static and no longer dependant on windows(1) ' got a solution now. How mental is this!? Perhaps this was out the Excel Team's area ' of control. The Excel team would not have let this mahem propagte. End Sub