Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Call Macro help!

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi everyone,

    OK heres what I'm trying to do:

    I have a workbook (wrkbook1) with code that opens another workbook(wrkbook2), and copies one of the sheets to wrkbook1. The sheet i am copying is named "copysheet" and contains a piece of code called "butt*******".

    after the sheet is copy, the same running macro tries to call "butt*******". I thought it should work because if the worksheet opens and copies, the code will be there. BUT, the macro stops before it does anything because it cant find the "butt*******" macro! (this is ofcourse because the sheet with the code hasnt been copied yet) I get the error not defined.

    Is there a way to make the macro run anyway, then it "SHOULD" work?

    Thanks

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    post your code i will have a look.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    in the first workbook, named "wrkbook1" there is this code:

    sub opencopy()
    currbook=Activeworkbook.name

    'Open
    Workbooks.Open Filename:= _
    "S:wrkbook2.xls", ReadOnly:=True
    Sheets("copysheet").Select
    Sheets("copysheet").Move Before:=Workbooks(currbook).Worksheets(Worksheets.Count)
    Sheets("copysheet").Unprotect

    call butt*******
    end sub


    then the 2nd workbook named: "wrkbook2" contains the sheet being copied, named "copysheet". "copysheet" has the following code in it (code is NOT in a module, but on the sheet):

    sub butt*******()
    msgbox("code worked")
    end sub


    so, as you can see, from the first workbooks code, IF the sheet is copied, the butt******* code will be available for execution, but the macro doesnt do ANYTHING because before it even runs, the code errors because it cant find butt******* yet.

    Thanks

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    try

    Application.Run "Book2.xls!butt*******"

    where book2 is where the butt******* macro is sorry i took so long to respond having lunch!!

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    brettvba,

    that worked, but one part i left out and still have a question about.

    the 2nd workbook has ONLY one worksheet (copysheet) and i did this purposely so that when the sheet is moved, it will close automatically.

    When i use your code, it opens another copy of the workbook...instead of going through the hassle of adding more code, is there ANY way to refer to the code on the copysheet in wrkbook1 after its copied over? This would save me some pain Id like to ONLY refer to wrkbook2 to copy the sheet over, and from then on, use the code on the copysheet in wrkbook1.

    thanks again

    [ This Message was edited by: robfo0 on 2002-04-18 19:28 ]

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sub opencopy()
    currbook = ActiveWorkbook.Name

    'Open
    Workbooks.Open Filename:= _
    "w:book2.xls" ', ReadOnly:=True
    Sheets("copysheet").Select
    Sheets("copysheet").Move Before:=Workbooks(currbook).Worksheets(Worksheets.Count)
    Sheets("copysheet").Unprotect

    Application.Run (currbook) & "!sheet4.butt*******"
    ' assuming you have 3 sheets in book1
    End Sub

    Hope that helps it works for me depends on how many sheets you have in book 1 or your book name..

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    or if you go into book2 and change the "(name)" of copysheet to copysheet you can run the macro like this

    Sub opencopy()
    currbook = ActiveWorkbook.Name

    'Open
    Workbooks.Open Filename:= _
    "w:book2.xls" ', ReadOnly:=True
    Sheets("copysheet").Select
    Sheets("copysheet").Move Before:=Workbooks(currbook).Worksheets(Worksheets.Count)
    Sheets("copysheet").Unprotect

    Application.Run (currbook) & "!copysheet.butt*******"
    ' assuming you have 3 sheets in book1
    End Sub

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    yes! the 2nd is what i was trying to do, works great now, thanks so very much

Some videos you may like

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
  •