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

[cvincent]

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.

 

[p45cal]

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?

 

[cvincent]

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.

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

Appreciate any help or suggestions. Thank you.

 

[p45cal]

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?:
?Range("SORT_RANGE").address
hopefully you don't get an error (if so halt the code and try again)

?Range("SORT_RANGE").parent.name
is it the right name?

?Range("SORT_RANGE").parent.parent.fullname
detailed examination of the result versus the open workbook needed.

?names("SORT_RANGE").RefersTo
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?

 

[Norie]

Which worksheet is the named range on?

 

[cvincent]

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.