May 13 2022 11:19 AM
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
May 13 2022 11:35 AM
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
May 16 2022 07:33 AM
May 16 2022 08:02 AM
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.
May 16 2022 09:09 AM - edited May 16 2022 10:06 AM
@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.
May 16 2022 09:49 AM
SolutionThank 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
May 16 2022 09:57 AM
May 16 2022 09:49 AM
SolutionThank 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