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?
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