1

I currently have a workbook with multiple sheets, and i am trying to apply FreezePanes function on most of my different sheets. However one of my sheets i want to apply a different rule on it so it only freezes the first 2 columns instead of 7 columns like all the other sheets.

My "Pivots" tab is the one i want to apply a separate rule.

My code atm is:

  Sub FreezePanes()

  sheetlist = Array("1", "2", "3", "4", "5", "6", "7", "8")
  'this is used so that all sheets freeze

For i = LBound(sheetlist) To UBound(sheetlist)
     Worksheets(sheetlist(i)).Activate
     ' The above is the loop that will go through all the different items in the array to direct it at each worksheet

     Columns("E:E").Columns.Group
     Columns("H:N").Columns.Group 'to change
     Columns("R:S").Columns.Group 'to change

     Columns("H:H").Select
     ActiveWindow.FreezePanes = True
    'code to freeze column H on those particular items

    Range("A7").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.AutoFilter
    'code to apply filter on 

ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1

  Next

 Worksheets("Pivots").Activate
 Columns("A:A").Select
 ActiveWindow.FreezePanes = True
'code to apply freeze panes only on first two columns
End Sub

I also have the following code that helps split it but it doesn't look as neat.

  Sub test()

  Worksheets("Pivots").Activate
  ActiveWindow.SplitColumn = 2
  ActiveWindow.SplitRow = 0

  End Sub
Blackbutterfly
  • 45
  • 5
  • 1
    [Don't use `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba), make sure to explicitly give the worksheet for the `Columns()` and `Range()`s – BruceWayne Jun 05 '18 at 04:42
  • 1
    @BruceWayne - you can only FreezePanes on the ActiveWindow so at least one activate is necessary for each worksheet. –  Jun 05 '18 at 04:48
  • 1
    @Jeeped while that is true, ultimately it is a good practice to avoid using it in a long run. (e.g. in OP's Answer he's using `.Select` as well which could have been easily avoided. I get your point and yes, freezepanes can't avoid using `ActiveWindow`, but I still think it's a good thing that he got reminded to try to avoid using these specific elements. – Samuel Hulla Jun 05 '18 at 07:20
  • 1
    Possible duplicate of [excel vba freeze pane without select](https://stackoverflow.com/questions/34094445/excel-vba-freeze-pane-without-select) – Samuel Hulla Jun 05 '18 at 07:32

1 Answers1

1

I managed to find out what was wrong.

Its all good now.

 Worksheets("Pivots").Activate
 Columns("C:C").Select
 ActiveWindow.FreezePanes = True
'code to apply freeze panes only on first two columns
 End Sub
Blackbutterfly
  • 45
  • 5