Macro in workbook fails when other workbooks are open - Runtime Error 1004

Question

Macro works fine when it is the only workbook open, but when any other workbook is open it fails. I am sure this is an easy fix but I can't figure it out. Thank you in advance.

Sub Sort_Leaders()
Dim wb As Workbook: Set wb = ThisWorkbook
wb.Worksheets("TABLE").ListObjects("Table1").Sort.SortFields.Clear
wb.Worksheets("TABLE").ListObjects("Table1").Sort.SortFields.Add _
    Key:=Range("Table1[[#All],[Rank]]"), SortOn:=xlSortOnValues, Order:= _
    xlAscending, DataOption:=xlSortNormal
With wb.Worksheets("TABLE").ListObjects("Table1").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Call test
End Sub

"*it fails*" Meaning exactly what? Excel crashes? Computer crashes? Error message (if so, what and on what line)? Wrong result? Also, you neglected to add a [Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). Please edit your question to add that also. — Ron Rosenfeld, Jun 19 '22 at 16:38
@RonRosenfeld, Apologies. When I run it with another workbook open the error is runtime error 1004: Method Range of Object Global Failed. The workbook is very large with a majority of the data not impacting the macro. Let me clean up the workbook and share. — kwmoore1218, Jun 19 '22 at 17:05

score 0 · Answer 1 · answered Jun 20 '22 at 01:29

VBA: Sort an Excel Table (`ListObject`)

A Quick ("Easy") Fix

Use

Key:=wb.Worksheets("TABLE").Range("Table1[[#All],[Rank]]")

instead of

Range("Table1[[#All],[Rank]]")

which is short for

ActiveSheet.Range("Table1[[#All],[Rank]]")

A Different Approach: Referencing Objects

Sub SortLeaders()
    
    Const wsName As String = "TABLE"
    Const tblName As String = "Table1"
    Const lcName As String = "Rank"
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    Dim tbl As ListObject: Set tbl = ws.ListObjects(tblName)
    Dim lc As ListColumn: Set lc = tbl.ListColumns(lcName)
    
    With tbl.Sort
        With .SortFields
            If .Count > 0 Then .Clear ' or just '.Clear'
            .Add lc.Range, xlSortOnValues, xlAscending, , xlSortNormal
        End With
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    'Call Test ' irrelevant
    
End Sub

Macro in workbook fails when other workbooks are open - Runtime Error 1004

1 Answers1

VBA: Sort an Excel Table (ListObject)

VBA: Sort an Excel Table (`ListObject`)