code only works in debugger

Put a DoEvents line after the first Selection.Copy and if that doesn't work put one after the second Selection.Copy

The ranges here have no worksheet references.

Range("P21:P43").Select
Selection.Copy

Sheets("Interpolated_Zero_Rate_new").Select
Range("A5:A75").Select

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
  xlNone, SkipBlanks:=False, Transpose:=False

Select allOpen in new window

Which sheet(s) are you copying from/pasting to?

If you are copying from 'BootStrapping' to 'Interpolated_Zero_Rate_new' try this.

Sheets("BootStrapping").Range("P21:P43").Copy

Sheets("Interpolated_Zero_Rate_new").Range("A5:A75").PasteSpecial Paste :=xlPasteValuesAndNumberFormats

Select allOpen in new window

Dear Mr. firefytr, it's yet to be seen if a DoEvents() is needed (note it's not Do Events), but in case you're not aware, what DoEvents does, among other things, is to temporarily yield to the processor within an event procedure. This allows the processor to catch up when it has to do external processing and it is not given enough time to complete the external process before the code moves on. It is often the reason that code will work when run in Debug but will not work when run normally.

I know what DoEvents does,Martin, and how it's spelled.  Thanks.  :)  While DoEvents (I have taken my misplaced space out, just for you! :) ) might be preferrable in some cases, this is not one of them.  It's just a simple copy/paste routine.  Better to turn off some application attributes and let it run.  We're not letting other processes run outside the scope of the application and need to wait for the code to finish, so it's not an issue here, thus DoEvents is not needed.

Regards,
Zack Barresse

Just so the whole back of 'tricks' is on the table :P

I can't say I disagree with anyone's comments.  I've found occassions when DoEvents/Wait works in special cases, as opposed to normal steps that shouldn't require it.

As I recently had this experience, I would suggest one more thing to do, and that's run Code Cleaner on the project.  Not only did my debugger kick in but when I hit F5 it was executing different lines than what was highlighted yellow, lol.

I don't know if Code Cleaner is need, but thought to tee it up if the other suggestions didn't get you there:
http://www.appspro.com/Utilities/CodeCleaner.htm

Dave

Hi Dave.  I think it's a safe a assumption that 90%+ of the code we all see that *magically works* when you use DoEvents is poorly written code, and not actually due to what is being processed at that time.

In addition to Bovey's CodeCleaner, MZ-Tools is another fantastic add-in for the VBIDE (http://www.mztools.com/index.aspx).

In the above code, since it is a simple loop, copy, paste, we are not waiting on other processes or applications, thus DoEvents would not be warranted.  In fact, you'd probably see a performance hit instead.  Because we want the code to execute as efficiently as possible (I hope that's what we all want!), we'll want these processes and commands to go quickly, thus execute and move on, and not yield any execution to any process.  We don't want to yield to the operating system, we want control, as we're not opening any outside processes which may take up any CPU speed/efficiency.  It might be advantageous if the OP were doing extreme multi-tasking, running multiple forms/code at once, or accessing other applications/processes which needed to run/load during run-time.  But as I said, this is not the case here.  In short - leave DoEvents out of it.  :)

Regards,
Zack Barresse