Results 1 to 8 of 8
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Freeze panes problem (2000)

    I'm using the following method to copy/ clone a worksheet template called "view", in preparation for filling with values:

    Dim sTarget
    Set sTarget = Worksheets.Add
    sTarget.Name = "testTemplate"
    Worksheets("view").Cells.Copy _
    Destination:=Worksheets(sTarget.Name).Range("A1")

    This works as desired, with all of the formatting appearing in the new "testTemplate" worksheet BUT what is lost is the frozen panes which were preset in the "view" template. This setting (on the "view" template) might vary according to the user. Is there any way to capture the setting and transfer it to the new "testTemplate" worksheet?

    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Freeze panes problem (2000)

    If you copy the worksheet instead of its cells, you'll preserve the frozen panes setting:

    Dim wsh As Worksheet
    Set wsh = Worksheets("view")
    wsh.Copy After:=wsh
    ActiveSheet.Name = "testTemplate"

  3. #3
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Freeze panes problem (2000)

    Panes are actually a property of the window that excel views a worksheet through and not the worksheet itself, which is why they are not copying over.

    use:

    "ActiveWindow.SplitColumn" to get the column to the left of the split or freeze
    "ActiveWindow.SplitRow" to get the row above the split or freeze.

    "ActiveWindow.SplitRow = 3" to reassign a row (and SplitColumn to reassign a column)

    "ActiveWindow.FreezePanes = True" to freeze - if that's what you want - once you have used the above. There does not seem to be a FreezeRow or FreezeColumn.

    HTH

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Freeze panes problem (2000)

    hmm. so my first comment above isn't entirely accurate then?

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Freeze panes problem (2000)

    It is part of the panes property:

    activewindow.panes(1).VisibleRange.address

    will give you the address of the cells in the pane, so selectinig the cell at the row:

    activewindow.panes(1).VisibleRange.rows.Count+1

    and in the column:
    activewindow.panes(1).VisibleRange.columns.Count+1

    of the new sheet and then doing:
    activewindow.freezepanes = true

    Will set the panes to the same.

    Steve

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Freeze panes problem (2000)

    Hi Brooke,

    You are correct in that split and freeze are properties of a window. You can have multiple windows on the same book, with different split / freeze settings in each window.

    If I copy the entire worksheet, the split / freeze settings in the first window are preserved in the copy (this is not necessarily the active window). The split / freeze settings of other windows are not copied.

    I like your solution - copying an entire worksheet also copies accumulated garbage, so it is not ideal. I had seen the SplitColumn and SplitRow properties, but hadn't found that you can combine them with FreezePanes.

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Freeze panes problem (2000)

    Thanks Hans, Brooke & Steve for all the information on this. I had no idea that the split/ freeze issue was so complex. I think I can cobble a good solution from the info you've offered though. One (possible) complication is that the "view" template might be secreted (hidden/ very hidden) once the user has established their preferences. This is why I had avoided use of the Copy After:= qualifier in the first place.

    Alan

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Freeze panes problem (2000)

    You can just use something like
    .copy before:=sheets(1)
    or
    .copy after:=sheets(1)

    There is always a sheets(1).

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •