Sort another sheet and leave A1 as active cell

[pwill]

Hi can anyone help?

I have a button on sheet1 that sorts range M2:W on sheet2.

When I run the sort from sheet1 with screenupdating false and active sheet2 before sorting starts and active sheet1 at the end of the sort and screenupdating true
I get a sight flicker of the screen on sheet1 and when I go to sheet2 cell A1 is the active cell.

If I remove activate sheet2 and sheet1.
The sort still works fine without the activation of the sheets and there is no screen flickering but when I go to sheet2 the range that was sorted has been left highlighted like with selecting a range. This happens after the .Apply when I stepped through the code.

My question is how can I leave out the activation of the sheets but still keep A1 as the active cell on sheet2.

Regards

pwill

 

[footoo]

It would help if you were to post your code.
But the following, for example, will not change the sheet2 selection :

With Worksheets("Sheet2")
    .[B2:B10].Sort Key1:=.[B2], Order1:=xlDescending, Header:=xlYes
End With

 

[pwill]

Hi footoo, thanks for that, much appreciated.

Sorry I didn't post the code, normally I would but I was at work and had to use my phone. I will add that line and post a before and after with the code I am using for u and others to see when I get home tonight from work.

pwill

 

[pwill]

It would help if you were to post your code.
But the following, for example, will not change the sheet2 selection :

With Worksheets("Sheet2")
    .[B2:B10].Sort Key1:=.[B2], Order1:=xlDescending, Header:=xlYes
End With

Hi footoo,
Could u have a look at my code, I'm struggling with where to add your line of code

Regards

pwill

With Worksheets(sheet2)
    SetRange = .Range("A2:K" & lRow)
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=Range("E2:E & lRow), _
    SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
    "0,1,-1,2,-2,3,-3", DataOption:=xlSortNormal

With Worksheets(Sheet2).Sort
    SetRange Range("A2:K & lRow)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End With

 

[footoo]

Sorry, can't work out a way of avoiding Sort.Apply when there is a CustomOrder.


This works :

With Worksheets("Sheet2")
    .Range("A2:K" & lRow).Sort Key1:=.[E2], Order1:=xlAscending, Header:=xlNo
End With

This doesn't work :

With Worksheets("Sheet2")
    .Range("A2:K" & lRow).Sort Key1:=.[E2], CustomOrder1:="0,1,-1,2,-2,3,-3", Header:=xlNo
End With

Unless someone else can help, you will have to live with this :

With Worksheets("Sheet2").Sort
    .SortFields.Clear
    .SortFields.Add Key:=[E2], CustomOrder:="0,1,-1,2,-2,3,-3"
    .SetRange Range("A2:K" & lRow)
    .Header = xlNo
    .Apply
End With

 

[Fluff]

How about

Sub mySort()
    Dim LstNum As Long
    ListNum = Application.CustomListCount + 2
    Application.AddCustomList Array("0", "1", "-1", "2", "-2", "3", "-3")
    
   With Worksheets("Pcode")
      .Range("A2:K" & 10).Sort key1:=.Range("E2"), order1:=xlAscending _
          , header:=xlNo, OrderCustom:=ListNum
   End With
    
    ListNum = ListNum - 1
    Application.DeleteCustomList ListNum
End Sub

 

[pwill]

Ok no worries thank you for having a look at it, hopefully some one might know another way around it?

pwill

 

[pwill]

Hi Fluff,
Thank you, just seen your post, can't try it out right now but will certainly give it a try tonight and let you know how I get on.

pwill

 

[pwill]

Hi Fluff, ran your code and works like a charm, very nice
That's what I love about excel, there's always a way.


pwill

 

[Fluff]

Glad we could help & thanks for the feedback.