Excel, create new window with same settings

This is the way that Excel works, and might be a bug or a "feature". It cannot be solved using the Excel GUI, so a VBA macro is required.

The article Gridlines & Freeze Panes Settings Lost in New Window – How to Fix It describes the problem and contains the solution as a VBA macro that does the following:

• Create the New Window
• Loops through all sheets in the new window and applies the following from the original window: Gridlines, Freeze Panes, and Headings.
• Activates the original sheet in the original and new window
• Arrange the windows in vertical side-by-side split screen view
• Scroll to the active tab in the workbook so you can view it.

In case that the article disappears in the future, here is 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

The article also contains a VBA macro to close the additional windows, so they wouldn't by mistake be saved and overwrite the original workbook:

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

The article also contains a section on installing the macros in your workbook, in case you have never worked before with VBA.