Making Sure Numbers Copy as Numbers

Written by Allen Wyatt (last updated April 17, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


2

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.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Headers and Footers in Subdocuments

If you use master and subdocuments, you may wonder how to control headers and footers in the subdocuments. This tip ...

Discover More

Getting Rid of the Ctrl+Click Message

When you add a hyperlink to a document, you can later click that link to display whatever is linked to. Well, you ...

Discover More

Gradient Prints as Stripes

When you print a graphic that includes a gradient, you may not get exactly the output you expect. This tip examines two ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Clean Up Your Macro List

Got a workbook cluttered with all sorts of macros? Delete them and you'll make your workbook easier to manage.

Discover More

Converting Strings to Numbers

When working with data in a macro, there are two broad categories you can manipulate: numbers and text. Sometimes you ...

Discover More

Using InputBox to Get Data

Need your macro to get some input from a user? The standard way to do this is with the InputBox function, described in ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 2 + 5?

2021-04-25 09:05:25

Willy Vanhaelen

When in the input box of the macro in my previous post you click "Cancel" you get a vba error warning which is not elegant. I fixed it and now the macro simply terminates as it is expected to do.

Sub DoCopy()
Dim tgt As Range, X As Long
On Error Resume Next
Set tgt = Application.InputBox(prompt:="enter target single cell", Type:=8)
If Err.Number = 424 Then Exit Sub
Set tgt = tgt.Resize(Selection.Rows.Count, Selection.Columns.Count)
tgt.NumberFormat = "general"
For X = 1 To Selection.Cells.Count
tgt(X) = Selection(X).Value
Next
End Sub

I also replaced
tgt.Cells(X) = Selection.Cells(X).Value
with
tgt(X) = Selection(X).Value

Both ranges are in fact arrays so it is not necessary to use the Cells property


2021-04-18 09:11:47

Willy Vanhaelen

This 7 lines macro can replace both TestCoy3 and DoCopy (5+25 lines of code although the way to use it s slightly different:

Sub docopy()
Dim tgt As Range, X As Long
Set tgt = Application.InputBox(prompt:="enter target single cell", Type:=8)
Set tgt = tgt.Resize(Selection.Rows.Count, Selection.Columns.Count)
tgt.NumberFormat = "general"
For X = 1 To Selection.Cells.Count
tgt.Cells(X) = Selection.Cells(X).Value
Next
End Sub

To use it, highlight the range to copy (can be a single cell. Then run the macro and enter the (single) target cell in the input box or point to that cell. That’s it.

Dates and formulas are preserved.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.