Frozen Panes

[Jenny]

I am copying the contenst of one worksheet to a blank
worksheet. The original worksheet has a frozen pane and
when I copy and paste that sheet to the blank sheet, the
frozen pane won't copy (everything else copies, but just
not the frozen panes).

How can I copy a frozen pane from one sheet to the next
along with the data?

Please help!

~Jenny

 

[Juan Pablo González]

I think the only way is using some VBA, like

Sub CopyFreezePanes()
Dim FP As Boolean
Dim Row As Long, Column As Long
With ActiveWindow
FP = .FreezePanes
Row = .SplitRow
Column = .SplitColumn
End With

'Go to the other sheet
Sheets("Sheet2").Activate

With ActiveWindow
.SplitRow = Row
.SplitColumn = Column
.FreezePanes = FP
End With
End Sub

 

[Doug Glancy]

Jenny,

Unless you can't, the easiest thing would be to copy the whole sheet with
all its settings, something like:

Sheets("Sheet1").Copy Before:=Sheets(2)

Otherwise, you could do something like this. It copies the FreezePane
settings from Sheet1 to Sheet2 (with help from a Dave Peterson post I
Googled):

Sub test()
Dim scroll_row As Long, scroll_col As Long

With ActiveWindow
ThisWorkbook.Worksheets("Sheet1").Activate
If .FreezePanes = True Then
scroll_row = .ScrollRow
scroll_col = .ScrollColumn
ThisWorkbook.Worksheets("Sheet2").Activate
Cells(scroll_row, scroll_col).Select
.FreezePanes = False
.FreezePanes = True
ElseIf .FreezePanes = False Then
ThisWorkbook.Worksheets("Sheet2").Activate
.FreezePanes = False
End If
End With
End Sub

hth,

Doug Glancy

 

[Gord Dibben]

Jenny

If you're copying ALL the contents use the Move/Copy sheet function found by
right-clicking a sheet tab.

Frozen Panes are the property of a worksheet.

They cannot be copied over just by selecting cells and pasting to another
sheet.

Gord Dibben Excel MVP