A Macro Question
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: A Macro Question

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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?

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    _________________
    Cheers, NateO

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

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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...

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com