Wait until Application.Calculate has finished
Further to my comments, you can use DoEvents with the Application.CalculationState. See this example
Application.CalculateIf Not Application.CalculationState = xlDone Then DoEventsEnd If'~~> Rest of the code.If you want you can also use a Do While Loop to check for Application.CalculationState
I would also recommend see this link
Topic: Application.CalculationState Property
Link: http://msdn.microsoft.com/en-us/library/bb220901%28v=office.12%29.aspx
Quote From the Above Link
Returns an XlCalculationState constant that indicates the calculation state of the application, for any calculations that are being performed in Microsoft Excel. Read-only.
Accepted answer did not worked for me because "DoEvents will not block until calculate completes and is effectively a no-op in your example code" I had the same issue. Finally this solution worked for me.
Do DoEvents Application.CalculateLoop While Not Application.CalculationState = xlDoneIn Manual calculation mode sometimes while loop goes into long running process. That is why added below line.
Application.Calculate