SOLVED

Method ‘Range’ of object ‘_Global’ failed

Copper Contributor

Excel Macro Error Run-time error ‘1004’

Method ‘Range’ of object ‘_Global’ failed

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                                                                                                                                                                                               

 

6 Replies

@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
@Hans Vogelaar 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.

@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.

@Hans Vogelaar I appreciate you taking a look. I have attached a csv with randomized data and macro enabled workbook. Each macro runs fine, but when combine into the 'RunReport' macro I keep getting that Range error.

best response confirmed by Grahmfs13 (Microsoft)
Solution

@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

 

That did it! I figured it was something simple like that I was missing.

Thank you so much for your help!
1 best response

Accepted Solutions
best response confirmed by Grahmfs13 (Microsoft)
Solution

@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

 

View solution in original post