How to use a macro to run macro in other excel files?

Hi all,

Hope all are well.

I want a macro which will be placed in an excel file. When that macro is run, it should run a macro by name "Analyze" one after another in all 10 files (point 1) and once all done shall display a message "Analysis Completed".

Please note below points.

1. There are 10 different files with names as below.

EMVS Alerts Log - Benelux.xlsm
EMVS Alerts Log - CEE.xlsm
EMVS Alerts Log - DACH.xlsm
EMVS Alerts Log - GB+I.xlsm
EMVS Alerts Log - Lower Case 1.xlsm
EMVS Alerts Log - Lower Case 2.xlsm
EMVS Alerts Log - Poland.xlsm
EMVS Alerts Log - Scanda.xlsm
EMVS Alerts Log - SouthernEuro.xlsm
EMVS Alerts Log - Spain.xlsm

2. The macro is to be run in above 10 files in sheets starting with name "All Data Summary - "
For example, the sheets are named as All Data Summary - Poland in file EMVS Alerts Log - Poland.xlsm, All Data Summary - Scanda in file EMVS Alerts Log - Scanda.xlsm and so on.

3. All 10 files in point 1 should be kept open before macro is run. If any file is not open, macro should give a message to open that file. I have an initial sample code for it as below which then continues with other requirement. Not sure if it can be used here.

Please Login or Register  to view this content.

4. The name of macro to be run in all 10 files is "Analyze"

5. Post running of macro, all files shall still remain open.

1 of the file in point 1 is attached for reference.

Can someone help with the requirement please?

Hi Bernie,

Apologies for delayed response.

The code works perfectly. Just 1 request - can you add a step in macro such that as and when Analyze macro is run in each file, the files should get saved

Perfect. Thanks a bunch Bernie

Hi again Bernie,

Thanks for the additional line in code to close the workbooks. Its really helpful, however, I want those workbooks to remain open which has "Miscellaneous" under column "AF" while rest closed. Is this possible

Thanks for the response Bernie.

I did as instructed but now none of the files close. All remain open

Please Login or Register  to view this content.

Brilliant. Thanks a bunch Bernie