Retrieve value from a formula in cells into different merged cells and retrieved value can be edited
I use these two methods when running macros that do intensive work of reading/writing information to several sheets:

' hide the mouse pointer, which in some PCs makes macros very slow. put this at the top
' of your module
Private Declare PtrSafe Function ShowCursor Lib "USER32" (ByVal fShow As Integer) As Integer

' disable some Excel features while the macro is running to improve performance
Sub enableMacroOptimizations()

    Dim cursorCount As Integer

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.DisplayScrollBars = False
    ActiveWindow.DisplayWorkbookTabs = False
    Application.DisplayStatusBar = True
    Do
        cursorCount = ShowCursor(False)
    Loop While (cursorCount &gt;= 0)

End Sub

' enable Excel features disabled with enableMacroOptimizations()
Sub disableMacroOptimizations()

    Dim cursorCount As Integer

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.DisplayScrollBars = True
    ActiveWindow.DisplayWorkbookTabs = True
    Application.DisplayStatusBar = True
    Do
        cursorCount = ShowCursor(True)
    Loop While (cursorCount &lt; 0)

End Sub


This will improve the performance of your macros no matter what. We have used this code to reduce macro times from more than 10 minutes to just a few seconds. 

Just call enableMacroOptimizations() at the top of you own intensive method, and then call disableMacroOptimizations() at the end (to leave the user workspace as found initially).