Retrieve value from a formula in cells into different merged cells and retrieved value can be edited

I have created this macro and having a small issue. I am very new to this VBA and did this with experts’ guides by googling it. I am creating an excel form for a work purpose at work. The Excel form has 7 sheets and each have named tables with different data.

• 1st sheet is the editable form, which retrieves data from the tables in the different sheets. This is the only visible sheet, and other sheets are protected and hidden,

• 1st sheet has hidden rows and columns those have logical formulas that assist me to retrieve my data from the other tables, so cells with formulas (mostly-vlookup, index, match, if, or) are in those rows and columns.

• I want the user to edit on the retrieved data, which is kind of suggestion to the user for to use it as-is or edit on it if required.

• I cannot write vlookup or loop kind of events, so I created below macro that allows me to retrieve the value of the formula as a text (not editable) in different cell, then retrieve that text to another merged and named cell as editable text.

• This macro works fine, but when I add more and more data into the Excel and add other macros, then it is quit slow.

Is it possible to this and make it function faster?

This macro applies to Sheet 1.

```
Private Sub Worksheet_Change(ByVal Target As Range)
'Value as text - kind of copy+pastespecial
If Range("F17").Value <> Range("E17").Text Then
Range("F17").FormulaR1C1 = Range("E17")
Range("func_R").FormulaR1C1 = Range("F17")
'func_R and other cells below with "_R" are my merged and main cells that users will see. E17 has a vlookup formula that retrieve data from a table in Sheet 2, with this macro I copy and pastpecial the formula in E17 in F17 as a text. Then to make it editable text I copy and paste it into the merged cell with "_R".Texts in cells with "_R" come from same table in sheet 2.
ElseIf Range("F18").Value <> Range("E18").Text Then
Range("F18").FormulaR1C1 = Range("E18")

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 >= 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 < 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).

StackExchange Code Review Q#133552, answer score: 5