[SOLVED] Close file and run macro from newly opened file

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

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"

--
Regards,
Tom Ogilvy




"Pradip Jain" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
>