Hi

I am running a macro In Excel 2003 Profressional that iteratively opens up a
list of spreadsheets and copies and aggregates the data. I recently started
receiving the Run time Error shown above. The code that blows up is shown
below. What is odd is that
a) it works the first few times
b) it is calling a Macro that works all the time
c) after the error, Excel is totally useless, you can't see the cursor
move,...

PLEASE HELP!!!
Thanks

For R = 1 To rowstoadd
Application.GoTo Reference:="RRcopy_to"
Selection.EntireRow.Insert
Range("RRcopy_from").Copy <==== ERROR <====
ActiveSheet.Paste
Next R

Hello,
firstly, the subject line of your post gets truncated, so I can't see
the entire error. Could you please put it in the message part of a post?

Also, can we please see the parts of code that most recently defined the
named ranges RRcopy_from and RRCopy_to? Or, if these are not defined in the
code but named ranges already existing in the sheet(s) then can you tell us
what the ranges refer to? By "most recently" I mean the last time before the
code where it fails.

If RRcopy_from is not defined, the copy will fail (but shouldn't hang Excel).

A possibility is that the insert row has caused Excel to hang, and the VBA
fails at the copy command because Excel is not responding. Guessing... maybe
the insert tries to push something off the bottom of the sheet into that
mysterious land that lies beyond row 65536. Interactively you'd get the
dialog box pop up warning you that something is being pushed off the edge, so
maybe the VBA isn't properly dealing with that. You don't happen to get a
dialog box appear somewhere, do you? Maybe hiding behind something else.
Maybe it's not truly hung, but is waiting for a response.

More along those lines... If RRcopy_to is a range defined by the number of
rows in RRcopy_from ... then if there's not enough rows left on the sheet to
define RRcopy_to ... what happens? Interactively it won't let you define a
range that goes beyond row 65536 - what happens when VBA code tries to define
a range like that?

Regardless of the cause, that Excel hangs instead of handing back control
might be reportable as a bug.


"RonKay" wrote:

Hi

I am running a macro In Excel 2003 Profressional that iteratively opens up a
list of spreadsheets and copies and aggregates the data. I recently started
receiving the Run time Error shown above. The code that blows up is shown
below. What is odd is that
a) it works the first few times
b) it is calling a Macro that works all the time
c) after the error, Excel is totally useless, you can't see the cursor
move,...

PLEASE HELP!!!
Thanks

For R = 1 To rowstoadd
Application.GoTo Reference:="RRcopy_to"
Selection.EntireRow.Insert
Range("RRcopy_from").Copy <==== ERROR <====
ActiveSheet.Paste
Next R