VBA Sorting in a named workbook

[jardenp]

Please see red notes in the code below for the error message. I'm stumped. I copied most of this from another macro that I'm pretty sure works. Please help.

Also, my coding for sorting is basically just a slight improvement on the macro recorder output. If anyone can point out a more concise/elegant way to do this, I'd really appreciate it!

Thanks,

JP in IN

'Variables    Dim MasterListFileName As String
        MasterListFileName = "TTrac Log Violations.xlsx"
    
    Dim MasterListSheetName As String
        MasterListSheetName = "Violation Master List"
    
    Dim MasterListFileLocation As String
        MasterListFileLocation = "C:\Users\XXXX\Desktop\TTrac Log Violations.xlsx"
        
'**************Other code in between these two sections, but the variables above aren't mentioned****************


    'Test if Teletrac Log Violations is open. If not, open it
    Dim TestWorkbook As Workbook


    Set TestWorkbook = Nothing
    On Error Resume Next
    Set TestWorkbook = Workbooks(MasterListFileName)
    On Error GoTo 0


    If TestWorkbook Is Nothing Then


        Workbooks.Open Filename:=MasterListFileLocation
    Else
        Windows(MasterListFileName).Activate
        Range("A1").Select
    End If


    Dim MLWB As Workbook
    Dim MLWS As Worksheet


    Set MLWB = ActiveWorkbook
    Set MLWS = MLWB.Sheets(MasterListSheetName)
    
    'Set the row to paste to (first blank on destination sheet)
    Dim AddRow As Long
    AddRow = Range("A100000").End(xlUp).Row + 1
    
    'Paste
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    
    'Set last row
    Dim MLLRow As Long
    MLLRow = Range("A10000").End(xlUp).Row
    
    'Extend point formula
    Range("E2").FormulaR1C1 = "=VLOOKUP(RC[-1],'Point List'!C[-4]:C[-3],2,FALSE)"
    Range("E2").AutoFill Destination:=Range("E2:E" & MLLRow)
    
    'Remove Duplicates
    Range("A2").Select
    ActiveSheet.Range("$A$1:$E$" & MLLRow).RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5), _
        Header:=xlYes
        
    'Sort by Last > First > Date (new to old) > Violation
    Range("A2").Select
[COLOR=#ff0000]    MLWB.MLWS.sort.SortFields.Clear '*********"Run Time Error 438 Object doesn't support this property or method" ERROR ON THIS LINE************[/COLOR]
    MLWB.MLWS.sort.SortFields.Add Key:= _
        Range("A2:A" & MLLRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    MLWB.MLWS.sort.SortFields.Add Key:= _
        Range("B2:B" & MLLRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    MLWB.MLWS.sort.SortFields.Add Key:= _
        Range("C2:C" & MLLRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    MLWB.MLWS.sort.SortFields.Add Key:= _
        Range("D2:D" & MLLRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With MLWB.MLWS.sort
        .SetRange Range("A1:E" & MLLRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[AlphaFrog]

The worksheet variable MLWS is a specific sheet with an already qualified parent workbook included.

Remove the MLWB from all the references MLWB.MLWS.Sort...

Here's the sort code cleaned up a bit (not tested):

    [color=darkblue]With[/color] MLWS.Sort
        [color=darkblue]With[/color] .Sortfields
            .Clear
            .Add Key:=MLWS.Range("A2:A" & MLLRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=MLWS.Range("B2:B" & MLLRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Add Key:=MLWS.Range("C2:C" & MLLRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
            .Add Key:=MLWS.Range("D2:D" & MLLRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        .SetRange MLWS.Range("A1:E" & MLLRow)
        .Header = xlYes
        .MatchCase = [color=darkblue]False[/color]
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    [color=darkblue]End[/color] [color=darkblue]With[/color]

 

[jardenp]

Thanks for the reply. With your code, I don't get an error anymore. However, the sort doesn't take place. I'm thinking I made a mistake in establishing MLWS or something. I replaced it with ActiveSheet and it works ok. I generally like to use names instead of current references, but unless I can figure out where I went wrong, I'll just have to go with ActiveSheet.Sort here.

Thanks again!

 

[AlphaFrog]

Try this to define the workbook and worksheet

[color=green]'Variables[/color]

    [color=darkblue]Const[/color] MasterListFileName [color=darkblue]As[/color] [color=darkblue]String[/color] = "TTrac Log Violations.xlsx"
    [color=darkblue]Const[/color] MasterListSheetName [color=darkblue]As[/color] [color=darkblue]String[/color] = "Violation Master List"
    [color=darkblue]Const[/color] MasterListFileLocation [color=darkblue]As[/color] [color=darkblue]String[/color] = "C:\Users\XXXX\Desktop\TTrac Log Violations.xlsx"
        
[color=green]'**************Other code in between these two sections, but the variables above aren't mentioned****************[/color]


[color=green]'    'Test if Teletrac Log Violations is open. If not, open it[/color]
[color=green]'    Dim TestWorkbook As Workbook[/color]
    
    [color=darkblue]Dim[/color] MLWB [color=darkblue]As[/color] Workbook
    [color=darkblue]Dim[/color] MLWS [color=darkblue]As[/color] Worksheet
    
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
        [color=darkblue]Set[/color] MLWB = Workbooks(MasterListFileName)
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0
    
    [color=darkblue]If[/color] MLWB [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        [color=darkblue]Set[/color] MLWB = Workbooks.Open(Filename:=MasterListFileLocation)
    [color=darkblue]Else[/color]
        MLWB.Activate
    [color=darkblue]End[/color] [color=darkblue]If[/color]
        
    [color=darkblue]Set[/color] MLWS = MLWB.Sheets(MasterListSheetName)
    Application.Goto MLWS.Range("A1")

 

[jardenp]

Beautiful. Thanks!