Sort with VBA works in general but not worksheet

[KillGorack]

I'm using the following code to sort a range.

ThisWorkbook.Sheets("Area").range("A1:D18").Sort Key1:=range("B2"), Order1:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

It works well when I place it in a module, but when I use this code in the worksheet.. it doesn't work at all.

Any idea?

 

[BSALV]

with a with ... end with and a point in front

    With ThisWorkbook.Sheets("Area").Range("A1:D18")
          .Sort Key1:=.Range("B2"), Order1:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
     End With

 

[SanjayGMusafir]

I'm using the following code to sort a range.

ThisWorkbook.Sheets("Area").range("A1:D18").Sort Key1:=range("B2"), Order1:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

It works well when I place it in a module, but when I use this code in the worksheet.. it doesn't work at all.

Any idea?

The code specifies the sheet it will work in... ThisWorkbook.Sheets("Area")

Remember to modify range and sort key in code to work as per your need in Sheet "Overall" range("A1:D18").Sort Key1:=range("B2")

Use this and try

ThisWorkbook.Sheets("Area").range("A1:D18").Sort Key1:=range("B2"), Order1:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ThisWorkbook.Sheets("Overall").range("A1:D18").Sort Key1:=range("B2"), Order1:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

 

[Fluff]

Or maybe

With ThisWorkbook.Sheets("Area")
    .range("A1:D18").Sort Key1:=.range("B2"), Order1:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

 

[KillGorack]

Thanks for the replies.. I had to show this in a meeting so I kinda hacked it together, in the sheet I was to do the sort. I placed the sort in the worksheet activate

It works.. In the area sheet vba.

Private Sub Worksheet_Activate()
    Dim rRow As Integer
    rRow = ThisWorkbook.Sheets("Area").Cells(Rows.Count, 1).End(xlUp).Row
    ThisWorkbook.Sheets("Area").range("A1:D" & rRow).Sort Key1:=range("B2"), Order1:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

edited the code

 

[Fluff]

Glad you sorted it & thanks for the feedback.