Close file and run macro from newly opened file

[Pradip Jain]

i have an excelfile of 20 MB (due to lot of data which cannot be deleted). It
has a macro in which simulation is run. it involves generation of random
numbers and i need huge number of trials to get meaningful results. problem
is when i try to run simulation when this 20 MB file is open it takes very
high time, about 20 seconds for one simulation. however if i copy the sheet
containing simulation code and save that in separate file, close the original
20 MB file and now run the macro, the simulation time is very fast. less than
1 second for 1 simulation.i can't do manual copy of the sheet and closing the
original file for every time i run simulation (the 20 MB file has various
sheets and various parameters can be changed and simulation done for that
scenario).

the simulation code is there in the original 20 mb file (let's call it
original20.xls). there is a button which is linked to this macro. when i
click on this button simulation starts. what i want to do is as that when i
click on simulate button on the original20.xls file the following happens:

the sheet named simulate is copied.
the sheet is pasted in a new xls file.
original20.xls is closed.
the new xls file genrated is named as simulresults.xls
there will be same button in the new file. but the macro associated with
this button will refer to the original20.xls. macro association should be
changed to macro contained in simulresults.xls
now when i click simulate button, actual simulation process starts.

Can anybody please help.

thanks

pradip

 

[Tom Ogilvy]

You can't run a macro from a closed workbook, so you would need both
workbooks open

worksheets("Simulate").copy

will put simulate and any control button in a new workbook. Any sheet level
code will be copied with the sheet.

You just need to write the click event code of the button to call a routine
in the original workbook.

Application.Run "Original20.xls!SimMacro"