VBA error while sorting

[boatbabe]

Hi, I have a (what I thought was) very simple line of code that is causing my macro to error if the macro is run from a different sheet to the sheet that the data is being worked on. I originally defined it within a block of With Sheets... code, but when it errored I defined the line itself but it is still erroring. The code is

        Worksheets("Accrued Holiday Report").Sort.SortFields.clear
        Worksheets("Accrued Holiday Report").Range("A:E").Sort Key1:=Range("E1"), Order1:=xlDescending

This works if I run the code from the Accrued Holiday Report sheet, but not if any other sheet is active.

The full code in case I have overlooked something earlier on is below. I am an amateur so any advice on tidying up the code will be gratefully received

Sub AccruedHolidayReport()
    Dim lastRow As Long    Dim LR As Long    Dim changetextcolour As Long    Dim changecellcolour As Long    Dim Found As Range                Application.ScreenUpdating = False        Worksheets("Accrued Holiday Report").Range("A:D").ClearContents    lastRow = Worksheets("Data").Range("a" & Rows.Count).End(xlUp).Row    Worksheets("Data").Range("A2:o2" & lastRow).Copy Worksheets("Accrued Holiday Report").Range("A1:O5000")        With Worksheets("Accrued Holiday Report")     .Range("A:A,C:C,E:E,G:N").Delete    End With                  With Worksheets("Accrued Holiday Report").Sort     .SortFields.Add Key:=Range("A1"), Order:=xlAscending     .SortFields.Add Key:=Range("B1"), Order:=xlAscending     .SetRange Range("A:E")     .Apply    End With            With Worksheets("Accrued Holiday Report")     lastRow = Worksheets("Accrued Holiday Report").Range("a" & Rows.Count).End(xlUp).Row        Worksheets("Accrued Holiday Report").Range("E1:E" & lastRow).FormulaR1C1 = "=IF(RC[-4]=R[1]C[-4],0,1)"        Worksheets("Accrued Holiday Report").Range("E1:E" & lastRow).Value = Worksheets("Accrued Holiday Report").Range("E1:E" & lastRow).Value     End With             Worksheets("Accrued Holiday Report").Sort.SortFields.clear        Worksheets("Accrued Holiday Report").Range("A:E").Sort Key1:=Range("E1"), Order1:=xlDescending          LR = Range("e" & Rows.Count).End(xlUp).Row    Set Found = Columns("e").Find(what:="0", LookIn:=xlValues, lookat:=xlWhole)    If Not Found Is Nothing Then Rows(Found.Row & ":" & LR).Delete      With Worksheets("Accrued Holiday Report")    .Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove    .Range("A1").FormulaR1C1 = "Name"    .Range("B1").FormulaR1C1 = "Last week worked"    .Range("D1").FormulaR1C1 = "Hours of holiday owed"        Columns("E:E").ClearContents    Columns("A:D").EntireColumn.AutoFit    Columns("D").NumberFormat = "0.00"   End With      With Worksheets("Accrued Holiday Report")    For changetextcolour = .Range("D" & Rows.Count).End(xlUp).Row To 2 Step -1        If Range("D" & changetextcolour).Value > 9 Then        .Range("D" & changetextcolour).Font.Color = -16776961    End If   Next changetextcolour       For changecellcolour = .Range("D" & Rows.Count).End(xlUp).Row To 2 Step -1        If Range("D" & changecellcolour).Value > 249 Then        .Range("D" & changecellcolour).Interior.Color = 65535      End If   Next changecellcolour      End With     Worksheets("Accrued Holiday Report").Select   Application.ScreenUpdating = True   End Sub

 

[pgc01]

Worksheets("Accrued Holiday Report").Range("A:E").Sort [B][COLOR=#8b4513]Key1:=Range("E1")[/COLOR][/B], Order1:=xlDescending

Hi

The Key should be in the same worksheet as the table. Try

Worksheets("Accrued Holiday Report").Range("A:E").Sort Key1:=Worksheets("Accrued Holiday Report").Range("E1"), Order1:=xlDescending

or, easier to read

With Worksheets("Accrued Holiday Report")
    .Range("A:E").Sort Key1:=.Range("E1"), Order1:=xlDescending
End With

 

[boatbabe]

Hi

The Key should be in the same worksheet as the table. Try

Worksheets("Accrued Holiday Report").Range("A:E").Sort Key1:=Worksheets("Accrued Holiday Report").Range("E1"), Order1:=xlDescending

or, easier to read

With Worksheets("Accrued Holiday Report")
    .Range("A:E").Sort Key1:=.Range("E1"), Order1:=xlDescending
End With

Thank you! I knew it was something simple I had missed

 

[pgc01]

You're welcome. Thanks for the feedback.