Making Sure Numbers Copy as Numbers

Mike notes that when copying numbers from one worksheet to another via a macro, the number often comes through as text. He wonders if he can address this issue through some VBA code.

When you copy information using a macro (using the .Copy method), the only time you should end up with a number "coming through" as text is if the number was formatted, in the source, as text. You can see that happening in a simple macro such as the following:

Sub TestCopy1()
    Dim s As Range
    Dim t As Range

    Set s = Sheets("Sheet2").Range("D7:D11")
    Set t = ActiveSheet.Range("A4")

    s.Copy
    t.Select
    ActiveSheet.Paste
End Sub

Anything that is in the source range (D7:D11) that is formatted as text will be pasted as text. Thus, if you have numbers formatted as text in the source, they will be formatted as text in the target.

The solution to this is to use the .PasteSpecial method instead of the .Paste method:

Sub TestCopy2()
    Dim s As Range
    Dim t As Range

    Set s = Sheets("Sheet2").Range("D7:D11")
    Set t = ActiveSheet.Range("A4")

    s.Copy
    t.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
      SkipBlanks:=False, Transpose:=False
    Selection.Value = Selection.Value
End Sub

The macro still sets up a source and target range, and then the .Copy method is used to copy the source. Next, the .PasteSpecial method is used to paste just the values to the target range. After this line of code is completed, the pasted range is selected automatically. Finally, the last line basically forces Excel to evaluate, again, what has been pasted into the cells. This is the final step that converts what might otherwise be text values into numeric values.

There is a drawback to using the .PasteSpecial method in this way—if your source data contains any dates or times, those are pasted as numeric values. So, you may need to do some additional formatting on your target values. This leads to an entirely different approach, one that doesn't use .Copy, .Paste, or .PasteSpecial. In this approach you use a subroutine to step through all the cells in a range and then copy them to where you want:

Sub TestCopy3()
    Dim s As Range
    Dim t As Range

    Set s = Sheets("Sheet2").Range("D7:D11")
    Set t = ActiveSheet.Range("A4")

    DoCopy(s, t)
End Sub

Sub DoCopy(Src As Range, Tgt As Range)
    Dim r As Range
    Dim c As Range
    Dim V As Variant
    Dim iRow As Integer
    Dim iCol As Integer

    If Tgt.Cells.Count = 1 Then
        iRow = 0
        For Each r In Src.Rows
            iCol = 0
            For Each c In r.Cells
                V = c.Value
                If IsNumeric(V) Then
                    If Not IsDate(V) Then V = V * 1
                End If
                Tgt.Offset(iRow, iCol).NumberFormat = "General"
                Tgt.Offset(iRow, iCol).Value = V
                iCol = iCol + 1
            Next c
            iRow = iRow + 1
        Next r
    Else
        MsgBox "Target must be a single cell"
    End If
End Sub

There are two macros in this code, with the first (TestCopy3) being used to set up and call the second (DoCopy). The TestCopy3 macro is very similar to those used earlier in this tip; it is the DoCopy subroutine that performs the heavy lifting for us. It requires that a source range (Src) and a target range (Tgt) be passed to it. The target range is a single cell that represents where you want the copy to be placed.

The macro steps through each cell of each row in the source range, checking to see if it is numeric. If it is, and it is not a date, then the value is multiplied by 1. This makes sure that the actual numeric conversion takes place, as required. Finally, the value is placed in the correctly offset cell from the specified target range.

Note, as well, that the macro sets the format of the target cell to General. This is done just in case the target cell was originally formatted as text. If you are sure that this won't be the case (or if you don't want all the copied cells to be formatted as General), then you can comment out or remove this line of the code.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13849) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.