Optimized Implementation of Copying Formula Cell Values to Specific Ranges in Excel VBA

Problem Background and Technical Challenges

In Excel VBA development, there is often a need to copy data from cells containing formulas to other worksheets. While the original code using Range.Copy Destination method works for regular data cells, for formula-containing cells, this method copies both the formula itself and the calculated values, which often doesn't meet actual business requirements.

Core Solution: PasteSpecial Method

To address the issue of copying formula cell values, the most direct solution is to split the single-step Copy Destination operation into two steps: first perform the Copy operation, then use the PasteSpecial xlPasteValues method at the target location. The advantage of this approach lies in its precise control over pasted content, retaining only the calculated results of cells while ignoring the formula structure.

Basic implementation example:

Range("B3:B65536").Copy
Sheets("DB").Range("B" & lastrow).PasteSpecial xlPasteValues

Code Optimization and Robustness Improvements

The original code has several optimizable points, including hard-coded row limits, repetitive code structures, and potential compatibility issues. By introducing array mapping and loop structures, code maintainability and execution efficiency can be significantly improved.

Optimized complete implementation:

Sub Get_Data()
    Dim lastrowDB As Long, lastrow As Long
    Dim arr1, arr2, i As Integer

    With Sheets("DB")
        lastrowDB = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With

    arr1 = Array("B", "C", "D", "E", "F", "AH", "AI", "AJ", "J", "P", "AF")
    arr2 = Array("B", "A", "C", "P", "D", "E", "G", "F", "H", "I", "J")

    For i = LBound(arr1) To UBound(arr1)
        With Sheets("Sheet1")
             lastrow = Application.Max(3, .Cells(.Rows.Count, arr1(i)).End(xlUp).Row)
             .Range(.Cells(3, arr1(i)), .Cells(lastrow, arr1(i))).Copy
             Sheets("DB").Range(arr2(i) & lastrowDB).PasteSpecial xlPasteValues
        End With
    Next
    Application.CutCopyMode = False
End Sub

Alternative Approach: Direct Assignment Method

In addition to using the Copy/PasteSpecial combination, the same functionality can be achieved through direct assignment. This method assigns the values of the source range directly to the target range, avoiding clipboard operations and potentially offering better performance in certain scenarios.

Implementation code:

Sheets("DB").Range(arr2(i) & lastrowDB).Resize(lastrow - 2).Value = _
      .Range(.Cells(3, arr1(i)), .Cells(lastrow, arr1(i))).Value

Practical Application Scenario Analysis

As mentioned in reference materials, similar copying requirements are quite common in automated process development. Particularly when using RPA tools like UiPath, the Copy Paste Range activity may exhibit abnormal behavior when set to the Value option. In such cases, employing VBA's PasteSpecial xlPasteValues method or direct assignment solutions can provide more reliable alternatives.

Technical Key Points Summary

When handling Excel formula cell value copying, several key technical points need attention: use PasteSpecial xlPasteValues to ensure only calculated values are pasted; optimize multi-column copying operations through array mapping; consider using direct assignment methods for performance improvement; properly handle dynamic positioning of target locations to avoid overwriting existing data.

Best Practice Recommendations

Based on practical development experience, it's recommended to: prioritize using array loop structures to improve code maintainability; test the advantages of direct assignment methods in performance-sensitive scenarios; ensure proper handling of Excel version compatibility issues; promptly clear clipboard status after copying operations are completed.