Method ‘Range’ of object ‘_Global’ failed

Excel Macro Error Run-time error ‘1004’

I have written a series of macros to import and analyze csv files to track the amount of time it takes for customers to receive services and flag situations where services take too long or data hasn’t been logged.

I have broken it apart into 5 different subs, one to allow the user to select the file and import, one to sort, one to format, and the last 2 to analyses and flag data. I then created a macro to call and run each in succession. When I run each macro sub individually, they all run without error. When I run the macro that combines them all together, I get the “Method ‘Range’ of object ‘_Global’ failed” error on this piece of code:

ActiveWorkbook.Worksheets("Sheet1").ListObjects("rsStaffTechActivity").Sort. _

SortFields.Add2 Key:=Range("rsStaffTechActivity[Participant]"), SortOn:= _

xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

When I click debug and click Continue ( F5), the code runs and completes without error. I’ve been looking for solutions for the past few days, and can’t seem to find anything that gets rid of the error. It is strange that everything is fine if I manually run each macro, but I can't combine them together. Any ideas would be appreciated.

The whole sub looks like this:

Sub SortImportedCSV()

' Step 2

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Sheet1").ListObjects("rsStaffTechActivity").Sort. _

SortFields.Add2 Key:=Range("rsStaffTechActivity[Participant]"), SortOn:= _

xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

ActiveWorkbook.Worksheets("Sheet1").ListObjects("rsStaffTechActivity").Sort. _

SortFields.Add2 Key:=Range("rsStaffTechActivity[Case_Note_Date]"), SortOn:= _

xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

ActiveWorkbook.Worksheets("Sheet1").ListObjects("rsStaffTechActivity").Sort. _

SortFields.Add2 Key:=Range("rsStaffTechActivity[Activity_Provided_Desc]"), _

SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Sheet1").ListObjects("rsStaffTechActivity"). _

Sort

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

End Sub

excel

Macros and VBA

HansVogelaar
May 16, 2022
Michael_2025

Thank you. The problem is that the query in Step 1 hasn't completely been refreshed yet when Step 2 is run. To avoid this, change the line

.Refresh BackgroundQuery:=True

near the end of ImportRsStaffTechActivityRev2 to

.Refresh BackgroundQuery:=False

6 Replies

HansVogelaar

MVP

May 13, 2022

Michael_2025

Does this work?

Sub SortImportedCSV()
' Step 2
    Dim w As Worksheet
    Dim t As ListObject
    Set w = ActiveWorkbook.Worksheets("Sheet1")
    Set t = w.ListObjects("rsStaffTechActivity")
    With t.Sort
        .SortFields.Clear
        .SortFields.Add2 _
            Key:=w.Range("rsStaffTechActivity[Participant]"), _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SortFields.Add2 _
            Key:=w.Range("rsStaffTechActivity[Case_Note_Date]"), _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SortFields.Add2 _
            Key:=w.Range("rsStaffTechActivity[Activity_Provided_Desc]"), _
            SortOn:=xlSortOnValues, _
            Order:=xlDescending, _
            DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Michael_2025
Copper Contributor
May 16, 2022
HansVogelaar Thank you for the quick response.

I am still getting the "Run-Time Error 1004
Method 'Range' of object '_Worksheet' failed" error.

it is on line
.SortFields.Add2 _
Key:=w.Range("rsStaffTechActivity[Participant]"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal

The most perplexing thing is that when I run each macro by itself, they work perfectly. But I am getting that range error when I run them all together.
- HansVogelaar
  MVP
  May 16, 2022
  Michael_2025
  
  Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

Forum Discussion

Method ‘Range’ of object ‘_Global’ failed

6 Replies

Resources