VBA copy range of values not destroying formulas

Question

Suppose every 5 rows in a sheet I have a formula SUM which sums up 5 rows above. So in A6 I have =SUM(A1:A5) and in A12 I have =SUM(A7:A11) and so on moving down. Is it possible to copy big range of values in Excel VBA to range A1:A1000 not destroying my formulas?

In other words. Wherever in destination is a value, overwrite it. If formula, leave it.

Is protecting formula cells a right direction to look for solution for my problem?

Update. I want to copy column E (contains only values) to column A (contains formulas in yellow cells, and values on white cells). I want to copy just the values to white cells, leaving yellow cells in peace (not destroying formulas).

Answer 1

If you do not need to preserve all values in E column, you can bulk copy using following code:

'Clear source rows corresponding to column A formulas 
Range("A:A").SpecialCells(xlCellTypeFormulas).Offset(0, 4).Clear
Range("E:E").Copy
'Paste special with Skip blanks 
Range("A1").PasteSpecial SkipBlanks:=True

Answer 2

Just test if a specified cell contains a formula before sum it.

For i = 1 To 100
    If Cells(i, 1).HasFormula Then

    Else
        Count = Count + Cells(i, 1)
    End If

Next

Answer 3

It can't be done via simple copy paste or formula as per my knowledge.

Try using a macro for this problem. The below macro works perfectly as per your requirements. You will need to update the source and target column names if it is other than "A" and "E" respectively.

Dim source As Range
Dim target As Range
Dim lastRow As Integer
Sub updateValuesNotFormulas()

    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Set source = Range("A1:A" & lastRow)

    lastRow = Range("E" & Rows.Count).End(xlUp).Row
    Set target = Range("E1:E" & lastRow)

    For Each srcCell In source
        If srcCell.HasFormula Then
            'Do Nothing
        Else
            targetRow = srcCell.Row
            targetColumn = target.Column
            srcCell.Value = Cells(targetRow, targetColumn).Value
        End If
    Next

End Sub

Answer 4

This proposed solution may provide the outcome you are looking for as long as the sum total cells in Column A, & Column E, line up in the same row. i.e. A6 & E6, A17 & E17, A33 & E33, etc. Know I am responding to an old post, but you never know who might be looking for a similar solution, cheers.

Sub valueAssignment()
'Copy E range to A range, without overwriting formulas
Dim Col As Long
Dim Row As Long
    For Row = 1 To 1000
        If Cells(Row, 1).HasFormula = False Then
            Cells(Row, 1).Value = Cells(Row, 5).Value
        End If
    Next Row
End Sub