Excel VBA Application run-time error 1004

Question

We have been provided an excel-vba file, which runs good on some machines, but on some machines following error is generated: run-time error 1004, application defined or user defined error, for following sequence:

sheetChoiceList.Sort.SortFields.Add key:=ThisWorkbook.Names("ChoiceList_AllChoices").RefersToRange.Columns(6), _
    SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

Sub:

Sub SortChoiceListForCurrentQuestionChoiceList()

' Sorts the Choice List based on the question type of the current question
' A formula in the sheet determines if the choice is applicable to the current question, returning a TRUE/FALSE
' A dyanamic named range uses the count of TRUE values to determine the number of rows extending down from row 2
' The sort is necessary to ensure that the TRUE values start on row 2

sheetChoiceList.Sort.SortFields.Clear
sheetChoiceList.Sort.SortFields.Add key:=ThisWorkbook.Names("ChoiceList_AllChoices").RefersToRange.Columns(6), _
                                                               SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With sheetChoiceList.Sort
    .SetRange ThisWorkbook.Names("ChoiceList_AllChoices").RefersToRange
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

Eby Jacob Eby Jacob · Accepted Answer · 2018-04-05T21:11:52

This could have caused due to various reasons.

1) It could be, because the macro is recorded at the worksheet level. Right click on the modules node on the VBA project window, click on insert module then paste your macro in the new module (make sure you delete the one recorded at the worksheet level).

2) See if there is any protected cells or sheets in the excel, if there are any remove the protections

3) Check if there are any macro recorded at the worksheet level. If there are any macro at the worksheet level, move them to a module. Right click on the modules node on the VBA project window, click on insert module then paste your macro in the new module (make sure you delete the one recorded at the worksheet level).

Hope this helps!

Regards, Eby

Excel VBA Application run-time error 1004

1 Answers