☰

Excel 2010 VBA，多个新窗口，排列

tag-icon • tag-icon microsoft-excel-2010 vba
Excel 2010 VBA，多个新窗口，排列

需要以下代码的帮助。

我的工作簿可以包含许多工作表。每个工作表都有自己的用途。我喜欢为每个工作表创建一个新窗口，然后平铺它们。

我从 support.microsoft 中获取了以下内容并插入了我的代码：

  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

它可以工作，但是所有新窗口都显示第一个工作表。我希望每个窗口都显示下一个工作表。

另外，我希望新窗口从第二张工作表开始，因为我在循环结束时会得到一个额外的窗口。

我不能使用工作表名称，因为工作表名称经常更改。

非常感谢您的帮助。

答案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

相关内容