Gridlines & Freeze Panes Settings Lost in New Window – How to Fix It

Bottom line: Learn how to use a macro to create new windows and apply the existing gridlines, freeze panes, and headings settings.

Skill level: Intermediate

Video Tutorial

Download the Excel File

The example Excel file that contains the VBA code can be downloaded below.

Gridlines & Freeze Panes Disappear in a New Window

In a previous post I shared how to use the New Window feature in Excel to compare different worksheets in the same workbook. This is a popular post, and some people will be using this tip everyday.

However, as Jack and Tom pointed out, some settings like freeze panes and gridlines will NOT be transferred to the new window.

Here's an example. The original window on the left has gridlines turned off and freeze panes applied under row 1. The new “- 2” window on the right does not retain these settings.

If you close the original “- 1” first and/or save the additional “-2” window, then this will override any changes made to the window settings.

It's IMPORTANT to note that we are ONLY referring to these window level settings (freeze panes, gridlines, headings). Changes to the data and objects within the workbook will be applied immediately to all open windows of the same file. You don't have to worry about losing data or modifications you make to the workbook when two windows are open.

Which settings are changed?

Some window level settings will revert back to the default settings when the new window is created.

• Gridlines: The gridlines will default back to being enabled for all sheets.
• Freeze Panes: Any freeze panes applied to a sheet will be removed.
• Headings: If you turned the row/column headings off for any sheet, those will also be turned on.

These settings can be found on the View tab of the ribbon.

Losing these settings can be frustrating. It can be VERY time consuming to set them back to how you had them originally. So I created a macro that will help save you time when creating new windows.

The New Window Macro

The goal of this macro is to save time with creating a new window by applying existing window settings to all sheets in the new window. It can also create the split screen view.

I have another macro below that will close the new window(s) in the proper order and restore the maximized view of the original window.

How the macro works

Here's what the New Window Macro does:

1. Creates the New Window.
2. Loops through all sheets in the new window and applies all of the following settings from the original window: Gridlines, Freeze Panes, and Headings. The macro identifies the cell (row/column) that the freeze panes are applied to and applies it the same way to the new window.
3. Activates the original sheet in the original and new window. The sheets must be activated in the loop to apply the window level settings.
4. Arrange the windows in vertical side-by-side split screen view. This uses the Arrange method (Arrange All button).
5. Scroll to the active tab in the workbook so you can view it. The split screen can cause the active sheet tab to be out of view. So this prevents you from having to scroll to find it.

The New Window Macro VBA Code

The example workbook in the downloads section above contains the VBA code.

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

Macro to Close the Additional Windows

As I mentioned above, it's best to close the additional “-2”, “-3”, etc windows first so you don't lose any window settings. If working in split screen, you will likely want to maximize the original window as well.

I created another macro that does all this with the click of a button.

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

How to “Install” the Macros

It's best to add these macros to your Personal Macro Workbook so that you can use them on any open Excel file. Here are the basic setup steps.

1. Add the code to your Personal Macro Workbook

The first step is to add the code to your Personal Macro Workbook (PMW). You can do this by dragging the m_Window module from the example file into your PMW in the VB Editor.

Here is an article that explains how to copy/import VBA code between files.

If you don't have your PMW setup yet, then checkout my video series on getting started with the Personal Macro Workbook.

2. Add a Macro Button or Shortcut

Next you can add a macro button to the ribbon or Quick Access Toolbar (QAT) to run the macro. This is optional, but makes it much easier to quickly run the macros.

You can also create a custom keyboard shortcut to run the macro.

Please Fix This Issue!

I believe this issue with the window level settings is a bug that needs to be fixed. Here is a post on the Excel Uservoice site where you can vote for it.

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/14767614-keep-freeze-panes-setting-when-opening-a-second-wi

Conclusion

The New Window button is a fantastic feature that allows us to compare two or more sheets in the same workbook. However, the issue with losing the window level settings can be frustrating and time consuming.

I hope this set of macros saves you time when creating new windows. The macros can be modified to fit your needs. Please leave a comment below with questions or suggestions.

Thank you! 🙂