主页 / computer / 问题 / 1653458
Accepted
Bob Weeks
Asked: 2021-06-04 06:23:57 +0800 CST

Excel，创建具有相同设置的新窗口

  • 772

为现有工作簿创建新窗口时，某些设置不会转移到新窗口。对我来说最重要的是冻结窗格、行和列设置，以及网格线的可见性。其他人报告未观察到其他设置。有些人已经描述了使用宏的解决方案，但这对于我希望有一个简单解决方案的东西来说似乎是额外的复杂性。

这是使用 View | 现有工作簿上的新窗口命令。

microsoft-excel

1 个回答

  1. Best Answer

    这是 Excel 的工作方式，可能是错误或“功能”。它无法使用 Excel GUI 解决，因此需要 VBA 宏。

    文章 Gridlines & Freeze Panes Settings Lost in New Window – How to Fix It 描述了该问题，并将解决方案包含为 VBA 宏，该宏执行以下操作：

    • 创建新窗口
    • 循环浏览新窗口中的所有工作表并应用原始窗口中的以下内容：网格线、冻结窗格和标题。
    • 在原始窗口和新窗口中激活原始工作表
    • 在垂直并排分屏视图中排列窗口
    • 滚动到工作簿中的活动选项卡，以便您查看它。

    万一以后文章消失了，这里是VBA代码：

    Sub New_Window_Preserve_Settings()
'Create a new window and apply the grid line settings
'for each sheet.

Dim ws As Worksheet
Dim i As Long
Dim iWinCnt As Long
Dim bGrid As Boolean
Dim bPanes As Boolean
Dim bHeadings As Boolean
Dim iSplitRow As Long
Dim iSplitCol As Long
Dim iActive As Long
Dim iZoom As Long
Dim sSep As String

  Application.ScreenUpdating = False

  'Store the active sheet
  iActive = ActiveSheet.Index

  'Create new window
  ActiveWindow.NewWindow
  iWinCnt = ActiveWorkbook.Windows.Count
  
  'Set the separator based on the version of Excel
  'Office 365 now using a dash
  If InStr(":", ActiveWorkbook.Name) > 0 Then
    sSep = ":"
  Else
    sSep = "  -  "
  End If
  
  'Loop through worksheets of original workbook
  'and apply grid line settings to each sheet.
  For Each ws In ActiveWorkbook.Worksheets
    Windows(ActiveWorkbook.Name & sSep & "1").Activate
    ws.Activate
    
    'Store the properties
    bGrid = ActiveWindow.DisplayGridlines
    bHeadings = ActiveWindow.DisplayHeadings
    iZoom = ActiveWindow.Zoom
    
    'Get freeze panes
    bPanes = ActiveWindow.FreezePanes
    If bPanes Then
       iSplitRow = ActiveWindow.SplitRow
       iSplitCol = ActiveWindow.SplitColumn
    End If
    
    'Activate the new window and sheet in loop
    Windows(ActiveWorkbook.Name & sSep & iWinCnt).Activate
    Worksheets(ws.Index).Activate
    
    'Set properties
    With ActiveWindow
      .DisplayGridlines = bGrid
      .DisplayHeadings = bHeadings
      .Zoom = iZoom
      If bPanes Then
        .SplitRow = iSplitRow
        .SplitColumn = iSplitCol
        .FreezePanes = True
      End If
    End With
  Next ws
    
  'Activate original active sheet for the new window
  Worksheets(iActive).Activate
  
  'Activate the original active sheet for the original window
  Windows(ActiveWorkbook.Name & sSep & "1").Activate
  Worksheets(iActive).Activate
  
  'Split Screen View (optional)
  'The following section can be commented out if you don't want split screen.
  
    'Turn screen updating on for split screen
    Application.ScreenUpdating = True
     
    For i = iWinCnt To 1 Step -1
      Windows(ActiveWorkbook.Name & sSep & i).Activate
    Next i
    
    'Split view side-by-side vertical
    ActiveWorkbook.Windows.Arrange ArrangeStyle:=xlVertical
    
    'Scroll to active tab in original window
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    ActiveWindow.ScrollWorkbookTabs Sheets:=iActive
    
    'Scroll to active tab in new window
    Windows(ActiveWorkbook.Name & sSep & iWinCnt).Activate
    DoEvents
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    ActiveWindow.ScrollWorkbookTabs Sheets:=iActive
  
End Sub

    该文章还包含一个 VBA 宏来关闭其他窗口，因此它们不会被错误地保存并覆盖原始工作簿：

    Sub Close_Additional_Windows()
'Close additional windows and maximize original

Dim i As Long
Dim sSep As String

  'Set the separator based on the version of Excel
  'Office 365 now using a dash
  If InStr(":", ActiveWorkbook.Name) > 0 Then
    sSep = ":"
  Else
    sSep = "  -  "
  End If
  
  If ActiveWorkbook.Windows.Count > 1 Then
    For i = ActiveWorkbook.Windows.Count To 2 Step -1
      Windows(ActiveWorkbook.Name & sSep & i).Close
    Next i
  End If
  
  Windows(ActiveWorkbook.Name).WindowState = xlMaximized

End Sub

    本文还包含有关在工作簿中安装宏的部分，以防您以前从未使用过 VBA。

    • 2

相关问题