Making Sure Numbers Copy as Numbers

by Allen Wyatt
(last updated April 17, 2021)

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 Office 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

Changing the Height of a Font

Scaling the width of a font is easy to do with Word's formatting capabilities. Scaling the height of the fonts is not so ...

Discover More

Converting a Range of URLs to Hyperlinks

Converting a single URL into a hyperlink is easy. Converting hundreds or thousands can be much harder if you have to rely ...

Discover More

Identifying the Last Cell Changed in a Worksheet

When someone changes a cell in a worksheet, Excel normally goes along its merry way of keeping everything up to date. It ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Reversing Cell Contents

Macros are great at working with text. This tip presents an example that shows this versatility by reversing the contents ...

Discover More

Seeing the Difference on the Status Bar

Do you often need to know the difference between two values in your worksheet? This tip shares a quick little macro that ...

Discover More

Saving Changes when Closing

If your macro closes workbooks, you'll want to make sure that it will save any changes you made to the workbook. Here's ...

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}] 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 3 + 8?

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.