VBA, how to quickly copy cell formula and formatting?

Question

[Last conclusion] I found that I value(11)can sometimes copy formulas , but not always. Formulas like sumproductand sumifcannot be copied, but simple arithmetic operations can be copied. This is so strange! I still can’t understand why.

I know that if I only want to copy the values, the fastest way is

Range("A1").Value = Range("A2").Value

If I want to copy formatting and values, the fastest way is

Range("A1").Value(11) = Range("A2").Value(11)

If I want to copy formulas, the fastest way is

Range("A1").Formula = Range("A2").Formula

What if I want to copy the formula and formatting? (I have a lot of formatting in this cell, like conditional formatting, bold, underline, alignment, number display format, border style, etc., Therefore, it is impractical to transfer each of the styles one by one.)

Answer 1

I just found a simple answer.

Range("A1").Value(11) = Range("A2").Value(11)
Range("A1").Formula = Range("A2").Formula

Since it value(11)fails to copy some complex formulas (I don’t know why there must be an error in Excel), I added a second line to paste the formula again. This should ensure that all formulas are correctly copied.

Answer 2

How about sub like:

Sub copyFormattingAndFormulas(source As Range, target As Range)

    Dim sourceCell, targetCell As Range
    Dim i, j, lastRow, lastColumn As Integer
    lastRow = source.rows.count + source.row - 1
    lastColumn = source.columns.count + source.Column - 1
    For i = source.row To lastRow
        For j = source.Column To lastColumn
            Set sourceCell = Cells(source.row + i - 1, source.Column + j - 1)
            Set targetCell = Cells(target.row + i - 1, target.Column + j - 1)
            targetCell.value(11) = sourceCell.value(11)
            targetCell = sourceCell.Formula
        Next
    Next

End Sub

, , - , ( .value(11)!) .

Answer 3

To copy formulas with formatting in the original range and save relative formulas (for example, using Ctr-C and Ctr-V), use this copy and paste method:

Range("A1").Copy Range("A2")