Call Macro from WB1 but execute it in WB2

Sorry - VB/Macro newbie

I have a file (utility.xlsm) which contains code to;

  • loop through all .xls files in a user specified directory (have code, works fine)
  • execute a sub "rept_prep" on each of the opened WBs within the directory.
    • "rept_prep" is local to utility.xlsm
  • save and close each WB after the macro has been executed (have code, works fine)

Problem is;

1st step of "rept_prep" evaluates the WB's sheet names to execute different logic based upon what sheet it is on but the sheet names are the sheet names in utility.xlsm and not the WB that has been opened.

I am hoping it is something simple but I am stumped... So... How do I apply a macro from utility.xlsm (user opened) against WB(x) (opened by utility.xlsm)???

Thank you in advance.

Vishesh's picture

Check this