Open a template, make some changes to cells and then save the file

[billj]

Hello everyone,

I am thinking of making a macro which will open a template and change values to some cells. After the calculation is complete, I will save / close the file to a folder. I will repeat this process for 20 times: open the template, make changes to some cells and then save / close.

My question is: how can I know if the automatic calculation has been completed before the file is saved and closed? Do I need to reserve a time gap of say, 5 mins before I am really sure the calculation is done?

[TMS]

I guess that would depend on what the calculations are and how many there are. Most simple formulae will calculate instantly. But, if your Template has a large number of complex formulae with full column references and or array formulae, it can take a while.

How will you automate entering 20 sets of data?

Suggest you post a copy of the template and indicate what values you would be changing.


Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[billj]

Sorry to ask again. Thanks for the advice of the experienced.

If I ask the macro to open the spreadsheet, make changes to some cell values and then immediately ask the macro to save / close the file, what will happen to the results in the spreadsheet? Will the spreadsheet be closed immediately, or it will wait until the automatic calculation is completed? I need to understand how it works in this case.

[jmac1947]

Hi billj,

the statement "ActiveWorkbook.Save" will wait until the workbook has actually saved which will be after the calculations have concluded.

You could consider setting the calculation options in such a way that automatic recalculation is disabled and only performed on workbook saved. If you calcs are extensive this would make the process faster for end to end

Hope this helps however as Trevor suggested / requested you should post an example workbook as he described, most of us do not have the time to "build a spreadsheet that matches what we think you are asking"

Cheers

[billj]

Many thanks, everyone. It is good to know that the .Save will wait until the calculation has concluded.

I also found an article which uses the Application.CalculationState property to check if the calculation is done. The link is: