Wait until Application.Calculate has finished

Further to my comments, you can use DoEvents with the Application.CalculationState. See this example

If Not Application.CalculationState = xlDone Then
End 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.

Leave a Comment