simplify code

[GScott]

I have pieced together 2 pieces of code. One section runs a sort whilst the other un hides columns and applies a filter.

What I would like to do is clean up the code but am unsure how to do it. In one instance it refers to the Active Workbook.Worksheets and in another instance it refers to the sheet with its code name. I would like to achieve some consistency and prefer to refer to the sheet with its code name - but ActiveWorkbook does not seem to permit this.

Here it is:

Private Sub Workbook_Open()


    ' unhide columns, unfreeze, delete filters and then freeze top row and apply filters


        With Sheet3
             Activate
          If .AutoFilterMode Then .AutoFilterMode = False
             .Range("A1:AC1").AutoFilter     'turn filters on
             .Columns.Hidden = False    'unhide any columns
           Sheet3.Select
             .[F2].Select
        ActiveWindow.FreezePanes = False    'unfreeze panes
        ActiveWindow.FreezePanes = True
   
    End With
    
    ActiveWorkbook.Worksheets("Arr-Dep").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Arr-Dep").Sort.SortFields.Add Key:=Range("L2:L5000"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    
    ActiveWorkbook.Worksheets("Arr-Dep").Sort.SortFields.Add Key:=Range( _
        "Y2:Y5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Arr-Dep").Sort
        .SetRange Range("A1:AA5000")
        .Header = xlYes
         .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

 

[GTO]

Hi there,

As you are running this in the workbook's Open event, we are safe in assuming ActiveWorkbook will always refer to ThisWorkbook (ThisWorkbook is explicit and should be used instead of ActiveWorkbook; unless of course, we actually may need to run the code against another workbook - in which case we should probably set a reference to the other workbook anyways...).

Anyways, does "Arr-Dep" refer to the same worksheet as the CodeName Sheet3?

 

[GScott]

Hi there,

Anyways, does "Arr-Dep" refer to the same worksheet as the CodeName Sheet3?

Yes it does.
I woudl prefer to refer to the sheet as Sheet3 but the workheets property doesn't support Sortfields - as I understand it.

 

[GTO]

Sorry, I need to bail out, so this is untested. Hopefully this makes sense and is a little help at least...

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> example()<br>  <SPAN style="color:#007F00">' unhide columns, unfreeze, delete filters and then freeze top row and apply filters</SPAN><br><br>  <br>  <SPAN style="color:#00007F">With</SPAN> Sheet3<br>    .Activate <SPAN style="color:#007F00">' At least as posted, you were missing the leading dot.</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> .AutoFilterMode <SPAN style="color:#00007F">Then</SPAN> .AutoFilterMode = <SPAN style="color:#00007F">False</SPAN><br>    <br>    .Range("A1:AC1").AutoFilter     <SPAN style="color:#007F00">'turn filters on</SPAN><br>    .Columns.Hidden = <SPAN style="color:#00007F">False</SPAN>    <SPAN style="color:#007F00">'unhide any columns</SPAN><br>    <br>    <SPAN style="color:#007F00">'Sheet3.Select  'You already activated the sheet and you would leave the 'Sheet3' out, as we are</SPAN><br>                    <SPAN style="color:#007F00">'inside the With statement</SPAN><br>    <br>    <SPAN style="color:#007F00">' .[F2].Select I am not sure we need to be selecting/activating the cell, but if so, I would just use the Cells property or the Range.</SPAN><br>    <SPAN style="color:#007F00">'              There is nothing wrong with the shorthand notation for the Evaluate Meythod per se, it just seems to no advantage for what</SPAN><br>    <SPAN style="color:#007F00">'              we are doing here.</SPAN><br>    .Cells(2, "F").Select<br>    <br>    ActiveWindow.FreezePanes = <SPAN style="color:#00007F">False</SPAN>    <SPAN style="color:#007F00">'unfreeze panes</SPAN><br>    ActiveWindow.FreezePanes = <SPAN style="color:#00007F">True</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> .Sort.SortFields<br>      .Clear<br>      <SPAN style="color:#007F00">' Because we are inside the With for .Sort.SortFields, we can no longer refer to .Range, as the range belongs to the worksheet</SPAN><br>      .Add Key:=Sheet3.Range("L2:L5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal<br>      .Add Key:=Sheet3.Range("Y2:Y5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> .Sort<br>      .SetRange Sheet3.Range("A1:AA5000")<br>      .Header = xlYes<br>      .Orientation = xlTopToBottom<br>      .Apply<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>  <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[GScott]

Thanks a lot. Works as expected.
I understand what you have written and you have taught me something about the Sort object.

Sorry, I need to bail out, so this is untested. Hopefully this makes sense and is a little help at least...
With .Sort.SortFields
.Clear
' Because we are inside the With for .Sort.SortFields, we can no longer refer to .Range, as the range belongs to the worksheet
.Add Key:=Sheet3.Range("L2:L5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Add Key:=Sheet3.Range("Y2:Y5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

 

[GTO]

You are most welcome of course and glad that it helped

Mark