Methodology for calling another spreadsheet and copying its results

[AndrewAfresh2]

A client has a large file (spreadsheet 1) into which they input a, b & c and get result x.

They have asked for and I have created another file (spreadsheet 2) which can interact with the first spreadsheet and store a list of all the inputs and results, so they can compare different scenarios.

The problem is that, on my Windows 7 PC, spreadsheet 1 seems to take such a long time to calculate, that spreadsheet 2 stores the wrong figure, possibly because this PC is slower and/or spreadsheet 1 has 26 tabs. It works fine on a Windows 10 PC.

The method spreadsheet 2 uses is:

• Set application.calculation to manual
• Copy values into relevant cells of spreadsheet 1
• Issue worksheet.calculate for spreadsheet 1
• DoEvents
• Copy results into relevant cells of spreadsheet 2
• Set application.calculation to automatic

If you can suggest a method that is more reliable please tell me.

I would prefer to have an error raised if recalculate hasn't finished, than spreadsheet 2 grabs an out-of-date figure.

Thank you if you can help (I'm using Windows 7 professional 64 bit, Office 365 v16)

 

[ExcelGzh]

I am wondering if you are using the correct calculate syntax. Try Worksheets("Mysheet").Calculate. If you are calling this from another workbook you will need to include the workbook reference as well Workbooks("Myworkbook.xlsm").Worksheets("Mysheet").Calculate.

DoEvents might also be letting other things interfere with the process. Unless you really need to be doing other things in Excel while this is running you would be best off taking DoEvents out.