none
Arranging of sheets in workbook - Excel vba RRS feed

  • Question

  • Hello,

    I am looking for the solution, how to arrange sheets in one workbook window. The example is below.

    I don't wont to use two workbooks.


    VALL2

    Tuesday, June 4, 2013 11:08 AM

Answers

  • Sub TwoWindows()
    Dim wn As Window
    Dim wns(1 To 2) As Window
    Dim col As Collection
         For Each wn In ActiveWorkbook.Windows
                 cnt = cnt + 1
                 If cnt > 2 Then
                         wn.Close
                 Else
                         Set wns(cnt) = wn
                 End If
         Next
         If cnt = 1 Then
                 Set wns(2) = ActiveWindow.NewWindow
         End If
         Set col = New Collection
         For Each wn In Application.Windows
                 If wn.Visible Then
                         If wn.Parent Is ActiveWorkbook Then
                                 wn.WindowState = xlNormal
                         Else
                                 col.Add wn
                                 wn.Visible = False
                         End If
                 End If
         Next
         wns(1).Activate
         Application.Windows.Arrange ArrangeStyle:=xlVertical
         For i = col.Count To 1 Step -1
                 col(i).Visible = True
         Next
         For i = 2 To 1 Step -1
                 wns(i).Activate
         Next
    End Sub

    You could add code you ensure respective sheets are active in each window

    Peter Thornton

    • Marked as answer by VALL2 Wednesday, June 5, 2013 9:59 AM
    Tuesday, June 4, 2013 12:21 PM
    Moderator
  • Sub TwoWindows()
    Dim cnt As Long, i As Long
    Dim wn As Window
    Dim wns(1 To 2) As Window
    Dim col As Collection
        For Each wn In ActiveWorkbook.Windows
            cnt = cnt + 1
            If cnt > 2 Then
                wn.Close
            Else
                Set wns(cnt) = wn
            End If
        Next
        If cnt = 1 Then
            Set wns(2) = ActiveWindow.NewWindow
        End If
        Set col = New Collection
        For Each wn In Application.Windows
            If wn.Visible Then
                If wn.Parent Is ActiveWorkbook Then
                    wn.WindowState = xlNormal
                Else
                    col.Add wn
                    wn.Visible = False
                End If
            End If
        Next
        wns(2).Activate
        Worksheets("Sheet2").Activate
        wns(1).Activate
        Worksheets("Sheet1").Activate
        Application.Windows.Arrange ArrangeStyle:=xlVertical
        For i = col.Count To 1 Step -1
            col(i).Visible = True
        Next
        For i = 2 To 1 Step -1
            wns(i).Activate
        Next
        wns(2).Activate    ' activate preferred window
    End Sub

    Change the names "Sheet1" and "Sheet2" to suit. Also change which window you want activated to suit. Keep in mind when working with the Windows collection the windows order changes to reflect the order of most recently activated windows.

    Peter Thornton

    Wednesday, June 5, 2013 10:43 AM
    Moderator

All replies

  • Sub TwoWindows()
    Dim wn As Window
    Dim wns(1 To 2) As Window
    Dim col As Collection
         For Each wn In ActiveWorkbook.Windows
                 cnt = cnt + 1
                 If cnt > 2 Then
                         wn.Close
                 Else
                         Set wns(cnt) = wn
                 End If
         Next
         If cnt = 1 Then
                 Set wns(2) = ActiveWindow.NewWindow
         End If
         Set col = New Collection
         For Each wn In Application.Windows
                 If wn.Visible Then
                         If wn.Parent Is ActiveWorkbook Then
                                 wn.WindowState = xlNormal
                         Else
                                 col.Add wn
                                 wn.Visible = False
                         End If
                 End If
         Next
         wns(1).Activate
         Application.Windows.Arrange ArrangeStyle:=xlVertical
         For i = col.Count To 1 Step -1
                 col(i).Visible = True
         Next
         For i = 2 To 1 Step -1
                 wns(i).Activate
         Next
    End Sub

    You could add code you ensure respective sheets are active in each window

    Peter Thornton

    • Marked as answer by VALL2 Wednesday, June 5, 2013 9:59 AM
    Tuesday, June 4, 2013 12:21 PM
    Moderator
  • Hello, thanks for code. it works well.

    I have one more requirement. In second worksheets, I need aktivate sheets(2). for this reason I used code:

        Windows("Sešit1:2").Activate
        Sheets("List2").Select

    But I prefer some more suitable solution in your code.

    thanks.


    VALL2

    Wednesday, June 5, 2013 9:58 AM
  • Sub TwoWindows()
    Dim cnt As Long, i As Long
    Dim wn As Window
    Dim wns(1 To 2) As Window
    Dim col As Collection
        For Each wn In ActiveWorkbook.Windows
            cnt = cnt + 1
            If cnt > 2 Then
                wn.Close
            Else
                Set wns(cnt) = wn
            End If
        Next
        If cnt = 1 Then
            Set wns(2) = ActiveWindow.NewWindow
        End If
        Set col = New Collection
        For Each wn In Application.Windows
            If wn.Visible Then
                If wn.Parent Is ActiveWorkbook Then
                    wn.WindowState = xlNormal
                Else
                    col.Add wn
                    wn.Visible = False
                End If
            End If
        Next
        wns(2).Activate
        Worksheets("Sheet2").Activate
        wns(1).Activate
        Worksheets("Sheet1").Activate
        Application.Windows.Arrange ArrangeStyle:=xlVertical
        For i = col.Count To 1 Step -1
            col(i).Visible = True
        Next
        For i = 2 To 1 Step -1
            wns(i).Activate
        Next
        wns(2).Activate    ' activate preferred window
    End Sub

    Change the names "Sheet1" and "Sheet2" to suit. Also change which window you want activated to suit. Keep in mind when working with the Windows collection the windows order changes to reflect the order of most recently activated windows.

    Peter Thornton

    Wednesday, June 5, 2013 10:43 AM
    Moderator