How do you loop through multiple Excel "windows"

[biocentrism]

Under View-->Window-->New Window you can open a new "window" of the workbook you are working on. This adds a " - 1" and a " -2" to the name of each window in the upper bar header.

I am trying to figure out how to loop through each of the windows using the VBA.

For instance if my Workbook is called "Book1.xlsm" and I have 2 Windows displayed showing this workbook. And then I want to go to Window1 and activate Sheet1. And then I want to go to Window2 and activate Sheet2.

How would I write the code for this?

Thanks.

 

[GWteB]

In the Excel version I use (2013) the title bar's suffix of a duplicated window is a colon and a number like "book.xlsm:2". If your version displays it otherwise, you're supposed to adjust the code below. Anyway, this should do what you're after, provided both Sheet1 and Sheet2 exist.

Sub biocentrism()
    With ThisWorkbook.Windows
        If .Count > 1 Then
            .Item(.Parent.Name & ":1").Activate
            .Parent.Worksheets("Sheet1").Activate
            .Item(.Parent.Name & ":2").Activate
            .Parent.Worksheets("Sheet2").Activate
        End If
    End With
End Sub

 

[biocentrism]

I am looking for how to handle after 2013. Version 365 does not use the "book.xlsm:2" convention anymore.

 

[GWteB]

If your version displays it otherwise, you're supposed to adjust the code below.

So perhaps something like this (note the red colored amendments):

Sub biocentrism()
    With ThisWorkbook.Windows
        If .Count > 1 Then
            .Item(.Parent.Name & "-1").Activate
            .Parent.Worksheets("Sheet1").Activate
            .Item(.Parent.Name & "-2").Activate
            .Parent.Worksheets("Sheet2").Activate
        End If
    End With
End Sub

or ...

Sub biocentrism()
    With ThisWorkbook.Windows
        If .Count > 1 Then
            .Item(.Parent.Name & " - 1").Activate
            .Parent.Worksheets("Sheet1").Activate
            .Item(.Parent.Name & " - 2").Activate
            .Parent.Worksheets("Sheet2").Activate
        End If
    End With
End Sub

 

[biocentrism]

actually i just figured it out. tricky. so there are double spaces before and after the hyphen. so its:

.Item(.Parent.Name & " - 1").Activate

thanks both for your help!

 

[GWteB]

so there are double spaces before and after the hyphen

... as suggested in the second example in my previous post. Glad it's sorted and thanks for letting me know.

 

[biocentrism]

... as suggested in the second example in my previous post. Glad it's sorted and thanks for letting me know.

just to make sure others understand the syntax requires double spaces before and after the hyphen to work not just single space.

 

[GWteB]

Pasting your example in a text editor gave me [space] [hyphen] [space]
So if I understand correctly it should be: [space] [space] [hyphen] [space] [space]
which means this site has trimmed your text.
Thanks for replying on that

 

[biocentrism]

yeah ive been struggling with this for 6 months just because of a double space!