VBA Copy/Paste Cell Content (Error 13)

Question

I'm working with Excel 365 and try to copy cell contents in VBA.
In the following formula I get an error 13 because the target is of type 'Variant/Integer' and the source of 'Variant/String':

ActiveSheet.Cells(NewRow, ColumnCounter).Value = ActiveSheet.Cells(CurrentRow, ColumnCounter).Value

How can I fix this? Why is the target cell (of a newly inserted row) suddenly formatted as integer?

If I alternatively try:

tbl.ListRows(CurrentRow).Range.Copy
Set NewRow = tbl.ListRows.Add(TargetRow)
NewRow.Range.PasteSpecial xlPasteValuesAndNumberFormats

I get the error:

1004: paste special method of range class failed

Vityata Vityata · Accepted Answer · 2018-07-18T15:12:27

Open a completely new Excel and try this:

Public Sub HereComesTheCode()

    Dim a As Long: a = 1
    Dim b As Long: b = 2
    Dim c As Long: c = 3
    Dim d As Long: d = 4

    ActiveSheet.Cells(a, b).Value = ActiveSheet.Cells(c, d).Value

End Sub

Then check what are the values in ColumnCounter, CurrentRow and NewRow in your code.

VBA Copy/Paste Cell Content (Error 13)

1 Answers