VBA Error, Excel 07, Named Range, Goto, Application Fail


Board Regular
Aug 28, 2011
I have a very large file, with data in cells A12:AB12000, identified as a named range. The file contains many macros, and is used by multiple employees. Employees update the file daily, and utilize macros to sort by various columns using a macro button at the top of each column. All sorts are done utilizing the named range. At the end of the week they click a button to "submit" their file.
When they click the "submit" button, the macro saves the file to their computer, then sorts the data using a named data range, and saves the file with a password to a special report folder. All the macros work fine on my pc, but some employees keep getting error messages everytime a sort function is used in a macro. They receive an error message 1004, "Method Goto of object Application failed". If I change the named range in the macro to a range reference, the macros once again work fine.
This is wonderful when it works, but need to find out why this happening for some people and not others using the same file? Some are able to use the file with no problem for 1 or 2 weeks, then come the 3rd week, and they get the error message.
Thank you.

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
First port of call would be to examine the name's RefersTo: when it goes wrong. Is it referring to another workbook, no longer accessible perhaps?
Upvote 0
Thank you P45Cal. I don't believe that is the issue. It is is the right workbook, and the macro works in other's files with the exact same macros. Below is a portion of the macro. "SortRange" is the named range for A11:AB12000. When the macro hits "SORT RANGE", it stops. When i replace the named range with the actual reference, it works.

Is it possible, that as employees continue to add data, the file gets too big? Or is it possible they do not have enough memory on their PC to run the macros? Are they perhaps pasting data into the field that is messing up the macro? We send this template out to a number of individuals each month, and they work on it all month, submitting each Friday. There are certain individuals who continually have this problem, while others do not.

Application.Goto Reference:="SORT_RANGE"
ActiveWorkbook.Worksheets("ATB").Sort.SortFields.Add Key:=Range("A12:A12000") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin

Appreciate any help or suggestions. Thank you.
Upvote 0
I'm going to persevere with my original line of thought, the RefersTo:, as it's the most likely source of the problem. When a user next reports the failure, you need to experiment with the likes of the same/similar instructions in the Immediate Pane (trying first without halting the code) do you get sensible results with the following?:
hopefully you don't get an error (if so halt the code and try again)

is it the right name?

detailed examination of the result versus the open workbook needed.

could you post that here perhaps?

Application.Goto might struggle if the sheet in question is hidden.
Are there multiple workbooks open?
How is the name created? Manually? Updated by code perhaps? If so what's that code look like?
Upvote 0
Which worksheet is the named range on?
Upvote 0
Thank you for your response. I changed all the named ranges to cell references, and it seemed to work ok on Friday (except for one user who somehow managed to delete one of the named ranges). Last month seemed to have more problems as the files grew larger by the end of the month. If this happens again, I will refer to your suggestions. Appreciate your assistance.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back