Run-time error 1004

[pdberger]

Good afternoon --

The code below has run fine for months and months, and suddenly errors out.
The offending line appears to be the
Range("AddNewData").Select line, which gives the following error:
Method 'Range' of object '_Global' failed

Here's the code:

Sub AddNewDataPoint()
'
' AddNewDataPoint Macro
' Macro recorded 5/9/2005 by Peter D Berger
'
'
'Unprotect sheet
ActiveSheet.Unprotect ("MedMan")


Range("AddNewData").Select ' HERE'S THE SPOT!
Selection.Copy
ActiveWindow.LargeScroll Down:=-1
Range("A6").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=28
Rows("53:53").Select
Selection.ClearContents
Range("A5353").Select
Selection.Copy
Range("A53").Select
ActiveSheet.Paste

It goes on from there, but I don't think that contributes. The code was
written on Excel 2003, and is routinely used now by machines with Excel07.

Any help would be greatly appreciated. TIA

 

[JLGWhiz]

Did you check to see if the range name is still valid? Maybe it has been
deleted. That is assuming that "AddNewData" is a name rather than a
variable. If it is a variable is needs to be declared and assigned a value.

 

[pdberger]

JLG --

I did look. On that page, the range name is there, the value is '#REF', but
the correct range is listed down at the bottom of that window.

TIA

 

[Dave Peterson]

If this code is in a General module and the AddNewData isn't on the activesheet,
you'll have trouble.

worksheets("sheetwithAddNewDataNameHere").select
Range("AddNewData").Select

Or you could use:

application.goto Range("AddNewData"), scroll:=true 'or false

 

[JLGWhiz]

the correct range is listed down at the bottom of that window.

I don't understand this statement. Do you mean the bottom of the dialog box
windor for define name? That is where you verify the named range. Also,
with this:

the value is '#REF'

It could be that the range is trying to link to an old workbook. I don't
see any reason otherwise why the range reference wouldn't work.

 

[pdberger]

Found the problem, but it raised a new question. When this workbook opens,
it also opens another workbook stored on a central corporate server, from
which it downloads reference info for formulas.

On the references for the range names, all the page names ('Sheet1',
'Sheet2', etc.) were replaced by this reference workbook name! So the range,
instead of being "'Sheet2'!$A$1:$F$20" was "'Instrument Panel Reference
Data.xls'!$A$1:$F$20". The worksheets are password protected to prevent
users from changing formulas like this.

This problem has happened before -- EVERY sheet reference in every formula,
chart, and range reference gets changed to this remote ftp workbook file.

Any idea how that might happen? The only time it appears in a macro is
during a workbook_open sub that opens it so the workbook can extract data to
cells.

 

[JLGWhiz]

Without knowing what has happened with the workbooks, seeing all of the
relevant code, etc. it would just be a guess. But the thing that is certain
is that it does not just decide to do it on its own.

 

[Dave Peterson]

It sounds to me like you copied (or moved) a range of cells (or a worksheet)
from that "instrumenta panel" workbook into the other workbook.

And excel helped out by making sure that the names pointed back to the original
workbook.

I'd try to fix those names:

Here are some tools that'll help you:

Bill Manville's FindLink program:
http://www.oaltd.co.uk/MVP/Default.htm

And I like Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp