How to Run a Macro From WB1 in WB2 Against WB2

[GEdwards]

I have searched through many examples but cannot locate what I need.

1) I have a file "Invoice Template.xls" (WB1). Within it is a sheet named
"Invoice".
2) I have a file "Master List.xls" (WB2). Within it is a sheet named "All
Invoices".

From a macro called CloseXLS in WB1, I need to run a macro in WB2 called
SortList. This must sort the "All Invoices" sheet within WB2. I then need
to return control back to the initial macro CloseXLS to complete its work.

Within my CloseXLS macro I have tried the code...

Application.Run "Master Invoice List.xls'!SortList"

I cannot get it to work.

I am not worried about the sort code just how to call from the WB1 macro to
execute another macro in WB2 against WB2.

How can this be accomplished?

 

[GEdwards]

Forgot to mention that I am using Excel 2003.

 

[Gord Dibben]

"not work" means what?

Error or nothing?

Try Application.Run "Master Invoice List.xls!SortList"

Note the removed single quote.


Gord Dibben MS Excel MVP

 

[GEdwards]

By removing the single quotes as suggested results in the following error;

Run-time error ‘1004’:
The macro ‘Master Invoice List.xls!SortList’ cannot be found.

By leaving the code as I had it originally continues with the following
actions;

1. The macro CloseXLS in WB1 issues Application.Run "'Master Invoice
List.xls'!SortList"
2. CloseXLS in WB1 opens WB2 as required
3. When using DEBUG, the active workbook is still WB1, but it runs the
SortList macro on WB1 and not WB2 as I require it to be.

Once the SortList macro finishes I then need to return control back to the
initial macro CloseXLS in WB1 to complete its work.

 

[Gord Dibben]

I removed only one single quote.

Post your SortList code.

Is there anything in there that activates WB2 so the sort can be done on
activeworkbook sheet "All Invoices"


Gord

 

[GEdwards]

That was it. I did not activate WB2 within my sort.

Thanks for giving my head a shake!