[Excel VBA] How to Create and Control a New Window for the Same Workbook (NewWindow)

Excel’s “New Window” feature is incredibly useful when you want to input data on one sheet while viewing summary results on another sheet in real-time. It allows you to display the same workbook in two windows simultaneously, showing different sheets or cell ranges in each.

Using VBA, you can automate the creation and manipulation of these windows. In this article, I will explain how to use the NewWindow method to create a new window, store it in an object variable, arrange the windows, and switch the displayed sheets.

Complete VBA Code

Below is practical VBA code that creates a new window, arranges the original and new windows side-by-side, and displays different sheets in each.

Sub CreateAndArrangeNewWindow()

    ' Declare variables
    Dim targetBook As Workbook
    Dim newlyCreatedWindow As Window
    
    ' Set the workbook containing this macro as the target
    Set targetBook = ThisWorkbook
    
    ' Check if the workbook has at least 2 sheets just in case
    If targetBook.Worksheets.Count < 2 Then
        MsgBox "This macro requires at least 2 sheets.", vbExclamation
        Exit Sub
    End If

    ' --- 1. Create a new window and store it in an object variable ---
    ' The .NewWindow method returns the created Window object
    Set newlyCreatedWindow = targetBook.NewWindow
    
    ' --- 2. Display information about the new window ---
    ' The window Caption will look like "WorkbookName:2"
    MsgBox "Created new window: [" & newlyCreatedWindow.Caption & "]"
    
    ' --- 3. Arrange the two windows side-by-side ---
    targetBook.Windows.Arrange ArrangeStyle:=xlArrangeVertical
    
    ' --- 4. Display different sheets in each window ---
    ' Activate the original window (Window 1) and show Sheet1
    targetBook.Windows(1).Activate
    targetBook.Worksheets("Sheet1").Activate
    
    ' Activate the new window (Window 2) and show Sheet2
    targetBook.Windows(2).Activate
    targetBook.Worksheets("Sheet2").Activate

    ' To close the new window later, you can use .Close:
    ' newlyCreatedWindow.Close

End Sub

Key Points of the Code

1. Creating a New Window: .NewWindow

Set newlyCreatedWindow = targetBook.NewWindow

Executing the .NewWindow method of the Workbook object creates a new window displaying that workbook.

The important point is that this method returns the created Window object itself. By storing this in an object variable (newlyCreatedWindow) using the Set keyword, you can directly and reliably control that specific window from VBA later.

2. Arranging Windows: .Windows.Arrange

targetBook.Windows.Arrange ArrangeStyle:=xlArrangeVertical

The Windows property of the Workbook object returns a collection of all windows belonging to that workbook. Executing the .Arrange method on this collection allows you to organize the layout.

• ArrangeStyle:=xlArrangeVertical: Arranges windows side-by-side (left and right).
• ArrangeStyle:=xlArrangeHorizontal: Arranges windows top and bottom.

3. Controlling Specific Windows

targetBook.Windows(1).Activate
targetBook.Worksheets("Sheet1").Activate

You can target a specific window by specifying its index number, such as targetBook.Windows(1). By using .Activate to bring a window to the front, and then .Activate again to select a sheet within it, you can control exactly what is shown (e.g., “Show Sheet1 in the first window and Sheet2 in the second”).

Summary

The basics of controlling separate windows for the same workbook in VBA are as follows:

1. Execute myWorkbook.NewWindow to create a new window.
2. Store the returned Window object in a variable (Set newWin = ...).
3. Use the myWorkbook.Windows collection to arrange the layout or manipulate individual windows.

This technique is extremely helpful when building macros that require users to view multiple sheets or wide ranges of data simultaneously, allowing you to automatically set up a user-friendly screen layout.