Avatar of SuraDalbin
Flag for United States of America

asked on 

Excel 2010 - Freeze Panes and Zoom On New Window

Hello Experts,

I'd like your help in figuring out a better approach to accomplishing the following:


Open A new Window in Excel


Find the cell in each visible worksheet, of the active workbook, that contains freeze pane and find the zoom percentage for each  worksheet


Apply the same freeze pane to the cell found in step #2 and the same zoom percentage to all worksheets in the new window
I came across the code below, and added a line to ignore hidden tabs, but as you can test in the attached file, the freeze pane doesn't always get applied to the correct cell or column in the new window.  Also, it's not consistent how it gets misapplied, so I couldn't determine if it was being applied incorrectly in a sequential manner, or any other pattern.

Option Explicit

Sub CreateNewWindow()
  Dim iRow As Integer
  Dim iCol As Integer
  Dim rOldPos As Range
  Dim iZoom As Integer
  Dim wOldWindow As Window
  Dim wNewWindow As Window
  Dim sSheet As Worksheet
  Dim sOldSheet As Worksheet
  Set wOldWindow = ActiveWindow
  Set sOldSheet = ActiveSheet
  Set wNewWindow = ActiveWindow
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.DisplayAlerts = False
  For Each sSheet In ActiveWorkbook.Worksheets
    If sSheet.Visible = xlSheetVisible Then
      Set rOldPos = ActiveCell
      iRow = 0
      iCol = 0
        If wOldWindow.FreezePanes Then
          iRow = ActiveWindow.ScrollRow
          iCol = ActiveWindow.ScrollColumn
        End If
      iZoom = wOldWindow.Zoom
        If (iRow > 0) And (iCol > 0) Then
          Cells(iRow, iCol).Select
          wNewWindow.FreezePanes = True
        End If
      wNewWindow.Zoom = iZoom
      End If
  Next sSheet
  Set wOldWindow = Nothing
  Set wNewWindow = Nothing
  Set rOldPos = Nothing
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Application.DisplayAlerts = True
End Sub

Open in new window

In the attached excel file, I have highlighted in yellow the worksheets that have freeze panes and in each, I've also highlighted the row or cell that contains the freeze pane.

Once it's working correctly, I would like to include this code in my Excel ribbon, so as to use in any workbook.

Any help you can provide will be greatly appreciated.


Microsoft ExcelMicrosoft OfficeSpreadsheets

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon