VBA Keep Numbers Stored as Text after Paste

[Seba Robles]

Hello,

I have a macro that is copying data from one workbook to another and everything is working as it's supposed to.

However, there are some number values from the original source (from where I copy) that have leading zeroes and when the macro pastes that into the new workbook, the values are pasted without the leading zeroes.

If I do the copy paste manually, I do not loose the leading zeroes and the values are pasted as text (which is what I want). But when the macro does the same thing, it doesn't keep the values as text and it removes the leading zeroes.

Additional info: I only need two columns from the original source to be kept as text after pasting into the new workbook.

Do you know if there's a way around this in VBA?

Let me know if you need any additional information.

Thanks in advance!

 

[Joe4]

First, are you sure that the leading zeroes are really there, and it isn't just formatting?
Second, please post your VBA code.

 

[maabadi]

Please Upload your macro here and show example of data you want pasted an excel with XL2BB addin or image.

 

[Seba Robles]

This is a screenshot (with sample data) of where I'm copying from: Columns E and H have numbers stored as text

This is where I'm pasting the data into: If I do so manually (whether I paste special as values or do a normal paste) the Parent and Item Number columns (now in columns H and K) keep their leading zeroes. When the macro does it (whether the macro pastes as values or pastes it without any special format), it only pastes the number value.

This is my code

Spoiler

Sub UpdatePlan_Click()

Dim PLAN_ws As Worksheet
Dim SFL_ws As Worksheet
Dim Major As Integer
Dim ConvNum As Range

Set PLAN_ws = ThisWorkbook.ActiveSheet
' Major = PLAN_ws.Cells(3, 2).Value
Set SFL_ws = ThisWorkbook.Worksheets("SFL Raw Data")
Set ConvNum = SFL_ws.Range("ConvNum")

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.StatusBar = "Loading SFL raw data, please wait...."
    
    SFL_ws.Activate
    
    On Error Resume Next
    
    Selection.AutoFilter
    
        'Delete previous SFL raw data
        Range("D1").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.ClearContents
    
        Range("A3").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.ClearContents
    
    'Search and select SFL raw data Excel file
    Application.DefaultFilePath = "C:"
    f = Application.GetOpenFilename("ExcelWorkbook(*.*),*.*,", , "Select SFL file to import")
    If f = "False" Then Exit Sub
    
    'Load Data
    Set W = Workbooks.Open(f)
    Set W1 = W.ActiveSheet
    
        'Copy SFL raw data
        W1.Activate
        Range("A1").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
    
        'Paste SFL raw data in Supply Plan tool and convert text fields to numbers
        SFL_ws.Activate
        Range("D1").Select
        Selection.PasteSpecial Paste:=xlPasteValues
        
            ConvNum.Select
            With Selection
                .NumberFormat = "General"
                .Value = .Value
            End With
    
        'Drag formulas and convert to values
        Range("D1").Select
        Selection.End(xlDown).Offset(0, -1).Select
        Range(Selection, Selection.End(xlToLeft)).Select
        Range(Selection, Selection.End(xlUp)).Select
        Selection.FillDown

        Range("C3").Select
        Range(Selection, Selection.End(xlToLeft)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues

        Application.CutCopyMode = False
        Range("D1").Select
    
    'Remove unwanted business majors/keep only the selected business major
    Selection.AutoFilter
    
    Application.CutCopyMode = True
    W.Close False
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.StatusBar = "SFL raw data load complete."

End Sub

 

[Joe4]

Look at this section of code here:

            With Selection
                .NumberFormat = "General"
                .Value = .Value
            End With

After your are pasting, you are changing the Format to General and then changing to value!
I believe this where the conversion is happening. Try getting rid of the .NumberFormat line and see if that fixes it.

 

[Seba Robles]

Yes but that's for another set of columns that do have numbers that I want to keep as number values. It's not affecting the ones with the parent code or item number.

 

[Joe4]

Yes but that's for another set of columns that do have numbers that I want to keep as number values. It's not affecting the ones with the parent code or item number.

I don't think you need to worry about that. It should copy them over, "as-is", so I don't think you need to explicitly state that.
Test it out and see what happens.

 

[Seba Robles]

So I tested it out and it seems to be working. I actually moved the convert to number part elsewhere in the code and it's not affecting the two columns from my original post.

Thank you Joe!

 

[Joe4]

You are welcome.