Call Macro help!

robfo0

Active Member
Joined
Feb 19, 2002
Messages
281
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 "buttonclick".

after the sheet is copy, the same running macro tries to call "buttonclick". 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 "buttonclick" 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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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 buttonclick
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 buttonclick()
msgbox("code worked")
end sub


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

Thanks
 
Upvote 0
try

Application.Run "Book2.xls!buttonclick"

where book2 is where the buttonclick macro is sorry i took so long to respond having lunch!!
 
Upvote 0
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
 
Upvote 0
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.buttonclick"
' 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..
 
Upvote 0
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.buttonclick"
' assuming you have 3 sheets in book1
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top