A Macro Question

[kluitna]

This is more a question of curiousity.

Why does a macro sometimes reference an old workbook ie(the one it was created in) when the code has been copied to a new workbook. I have a macro that does this everytime I run it, it pops up a file dialog box, looking for its old file path. No where in the macro is a reference to a file path and in the same module there are macros that were copied from the same old workbook that work just fine. I find this quite odd.

Anyone know what causes this?

[NateO]

Not a solid idea....Show the naughty code.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-03-18 17:43 ]

[Juan Pablo González]

The common problem for this is a "manually" created toolbar, that hasn't been attached to the workbook. So, it just keeps looking for the "old" file...

I always try to create my toolbars on the fly (At opening, and delete them at close)

[kluitna]

Nate,

Here is the code in question:

Sub copy_top()
'
For i = 0 To 29
Range("c6:c17").Select
Selection.Copy
Sheets(1 + i).Select
Range("c6").Select
ActiveSheet.Paste
Next
Sheets(1).Select
End Sub
-------------------------------------------
Sub copy_bottom()
'
For i = 0 To 29
Range("B24:G35").Select
Selection.Copy
Sheets(1 + i).Select
Range("B24").Select
ActiveSheet.Paste
Next
Sheets(1).Select
End Sub

both macros reside in the same module and both were copied form the same workbook. But the the copy_top, trys to look for its old spread sheet like a lost puppy.

Juan, what you said makes since there are several macro tool bars tied to excel it self, from a previous user. What confuses me about this is it doe sit even when I run from VBA editor. Any ideas, this is more of an annoyance than anything else, the macro still does its job, its i just have to hit cancel for every sheet. Not something I want in my final product.

[NateO]

I think your post that Barrie just responded to is pertinent. If you have a link to a cell in another workbook that does not exist in the designated file path in the range c6:c17 you will get a dialog each time you try to paste the bad link.

Get rid of the bogus links and your troubles are all behind you.

Hope this is getting warmer...

Cheers, NateO

[ This Message was edited by: NateO on 2002-03-19 09:47 ]

[kluitna]

Nate,

I think you are right, That makes prefect sense now. And it just hit me how you remove the links. Duh.. If I am right you just clear the contents in the offending cells and save and the links should be gone.

I will give that a try anyway, seems to be the way to make it work...