Excel 2010 VBA, várias novas janelas, organize

Question

Precisa de ajuda com o código a seguir.

Minhas pastas de trabalho podem ter muitas planilhas. Cada planilha tem seu próprio propósito. Eu gosto de criar uma nova janela para cada planilha e, em seguida, azulejo-los.

Eu peguei o seguinte do support.microsoft e inseri meu código:

  Sub WorksheetLoop2()

     ' Declare Current as a worksheet object variable.
     Dim Current As Worksheet

     ' Loop through all of the worksheets in the active workbook.
     For Each Current In Worksheets

        ' Insert your code here.
        Sheets.select
         ActiveWindow.NewWindow
         Windows.Arrange ArrangeStyle:=xlTiled
        ' This line displays the worksheet name in a message box.
        MsgBox Current.Name
     Next

  End Sub

Funciona, mas todas as novas janelas mostram a primeira planilha. Gostaria que cada janela mostrasse a próxima planilha.

Além disso, gostaria que as novas janelas começassem na segunda planilha porque acabo com uma janela extra no final do loop.

Não consigo usar nomes de folhas porque os nomes das folhas são alterados de acordo com

Sua ajuda é muito apreciada.

    

Answer 1

Here's one way to iterate through the windows of the active workbook, set each to a particular sheet, and put a specific cell in the top left corner. 

Sub Arrange_windows()

'declarations
Dim a As Integer

    'loop through all windows of the active workbook
    'count backward so we end up with the windows arranged from
    '1ow to high after we arrange them at the end
    '"arrange" sequences windows from most recently active to least recently active
        For a = ActiveWorkbook.Windows.Count To 1 Step -1
        'For a = 1 To ActiveWorkbook.Windows.Count
            'activate window a
                ActiveWorkbook.Windows(ActiveWorkbook.Name & ":" & a).Activate
            'activate sheet a in the active window
                ActiveWorkbook.Sheets(a).Select

            'add logic here to decide what to do in the active window
            'one possibility is a "Select Case" statement:
            Select Case ActiveWindow.Index
                Case 1:
                    ActiveWorkbook.Sheets("Sheet1").Select
                    Application.Goto Reference:=Range("$A$1"), Scroll:=True
                Case 2:
                    ActiveWorkbook.Sheets("Sheet2").Select
                    Application.Goto Reference:=Range("$D$15"), Scroll:=True
                Case Else:
                    'do nothing
            End Select
        Next a

'arrange the windows sequentially from most
'recently active to least recently active
    ActiveWorkbook.Windows.Arrange (xlArrangeStyleTiled)

End Sub