Call Macro help!

[robfo0]

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

[brettvba]

post your code i will have a look.

[robfo0]

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

[brettvba]

try

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

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

[robfo0]

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 ]

[brettvba]

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

[brettvba]

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

[robfo0]

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