VBA Open Second workbook window, maintain formatting, fix the freeze pane section

willastrowalker

New Member
Joined
Aug 28, 2015
Messages
9
I've got some code that is intended to open up a second window (you know when you have the same workbook open on two separate screens? Super helpful...
But, whenever it opens, in its attempt to maintain the freeze pane structure, it adds in some actual split panes.. just want to keep the freeze pains and not add in the splits.. didn't see anyting like the .splitpane command for freeze panes.. any ideas?



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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Forum statistics

Threads
1,223,888
Messages
6,175,212
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top