How to make a VBA copy move values with trailing zero's

[philwojo]

I have some code that is moving data from a 'raw data' sheet to another sheet, but on the new sheet it is not moving the data with trailing zero's. How can I update this code to make sure it move all the raw data over as it is shown, I think I need to move it as Text, but I'm not fully sure how to do that.

Sub CopyRawdata()Dim copyRange As Range


Worksheets("PIF File Checker").Activate
Call Clearcells


Worksheets("Raw Data").Activate
   Set copyRange = Range("b2:b" & Range("A65536").End(xlUp).Row)
   With copyRange
       Sheets("PIF File Checker").Range("B7").Resize(.Rows.Count, .Columns.Count).Value = .Value
   End With
   
End Sub

Thanks,
Phil

 

[Joe4]

Are you sure that the value really has trailing zeroes, and it is not just a Custom Format being applied?
If so, then just apply the same Custom Format to the range you are posting to.

If it is formatted as Text, make sure that the range you are pasting to is also formatted as Text.

 

[philwojo]

Yes the data does have trailing zero's. I set the format on the destination sheet to be "text" for all cells. When I do a copy/paste directly in to that sheet it works as expected and keeps the trailing zero's.

But if I use the code above to go from another sheet to the destination sheet it seems to be overriding that formatting and removes the trailing zeros.

 

[Joe4]

Try copying like this instead. It seems to work for me:

Sub CopyRawdata()

Worksheets("PIF File Checker").Activate
Call Clearcells

Worksheets("Raw Data").Activate
Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row).Copy Sheets("PIF File Checker").Range("B7")
   
End Sub

 

[philwojo]

Same results.

Here is the data from the 'raw data' tab, it is all in one cell on that tab.

[TABLE="class: outer_border, width: 809"]
<tbody>[TR]
[TD="width: 209"]2,,,,,Activate,AP,,,,Add,,,2017-10-06 23:59:59,2017-11-05 23:59:59,,,0.00,0.00,USD,,,,,327,,INV# IDTI-10061 / 2017-07-21,Add,22152.00,1,Each,,,,,,,,,,Add,950,,,Add,,,,,,,VID|PNumber,Add,16454|327,,,[/TD]
[/TR]
</tbody>[/TABLE]


The 22152.00, that I changed to red font above, is the value not coming over correctly. I have other data sets with this same kind of value as well.

 

[philwojo]

I apologize, and not sure if this makes a difference or not, but I have a bit more code in there, so not sure if this is the problem. Here is the code I missed it earlier:

Sub CopyRawdata2()

Worksheets("PIF File Checker").Activate
Call Clearcells


Worksheets("Raw Data").Activate
Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row).Copy Sheets("PIF File Checker").Range("B7")
   
Worksheets("PIF File Checker").Activate
Call Text2ColSplit


End Sub

The other call is this:

Sub Text2ColSplit()    Range("b7:b6000").TextToColumns _
      Destination:=Range("b7:b6000"), _
      DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=False, _
      Comma:=True


End Sub

 

[Joe4]

Yes, it is not the copy that is dropping the trailing zeroes, it is your split macro.
In Text to Columns, you can designate the format of each field. You need to designate that field to be text. You completely left the field designation part out of your code.
If you turn on the Macro Recorder and record yourself manually splitting one of your values, and pick the format of each field, you will see the code that you need to add to your current code.

 

[philwojo]

Ok So I updated my 2nd bit of code to this, but it still isn't working. I'm not sure what I'm missing, the code runs, just doesn't appear to fix my issue.

Sub Text2ColSplit()    Worksheets("PIF File Checker").Activate
    
    Range("b7:bf6000").Select
    Selection.NumberFormat = "@"
    
    Range("b7:b6000").TextToColumns _
      Destination:=Range("b7:b6000"), _
      DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=False, _
      Comma:=True


End Sub

I tried searching around a bit, and I did the macro record and here is what I got from that:

Sub Macro1()'
' Macro1 Macro
'


'
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("B7"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, OtherChar _
        :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
        , 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _
        Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array( _
        25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), _
        Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array( _
        38, 1), Array(39, 1), Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), _
        Array(45, 1), Array(46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array( _
        51, 1), Array(52, 1), Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1)) _
        , TrailingMinusNumbers:=True


    Range("AD7").Select
    Selection.NumberFormat = "@"
End Sub

So, I don't think I need the "array" information, but I could be wrong, I figured it was those last 2 lines that I needed to set the cell formatting. I'm trying to learn, so I apologize if this is easy and I'm just missing this.

PHil

 

[Joe4]

No, this Array information is PRECISELY what you need. That is why I wanted you to record it, so you could add it to your code.

The format of it is: Array(Field Number, Data Format)
"General" is Data Format 1 and "Text" is Data Format 2
So, you need to identify which field this value is in, and change the Data Format from 1 to 2.
If you had done this when recording the Text to Columns, it would have recorded that for you. But it looks like you did not do that, as all the Data Formats are 1 (which the the default "General").

When you do the Text To Columns, when you get to Step 3, it shows you where all the breaks are. If you click in the header of each field, it will select that field, and then you can change the Column Data Format.
So do that, and change the appropriate field where these trailing zeroes are from "General" to "Text".
Then, when you are finished, copy everything after the Comma:=True part of that command to the end of your current Text to Columns section of code.

That should do what you want.

 

[philwojo]

I see that now, I actually was looking at that when you typed your response as I tried to go through it and figure it out on my own.

This is now what I ended up with and it appears to be working, again not sure if it is the most elegant code, but it at least works.

Sub Text2ColSplit()    Worksheets("PIF File Checker").Activate
    
    Range("b7:bf6000").Select
    Selection.NumberFormat = "@"
    
    Range("b7:b6000").TextToColumns _
      Destination:=Range("b7:b6000"), _
      DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=False, _
      Comma:=True, _
        FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), _
        Array(7, 2), Array(8, 2), Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2 _
        ), Array(14, 2), Array(15, 2), Array(16, 2), Array(17, 2), Array(18, 2), Array(19, 2), Array _
        (20, 2), Array(21, 2), Array(22, 2), Array(23, 2), Array(24, 2), Array(25, 2), Array(26, 2), _
        Array(27, 2), Array(28, 2), Array(29, 2), Array(30, 2), Array(31, 2), Array(32, 2), Array( _
        33, 2), Array(34, 2), Array(35, 2), Array(36, 2), Array(37, 2), Array(38, 2), Array(39, 2), _
        Array(40, 2), Array(41, 2), Array(42, 2), Array(43, 2), Array(44, 2), Array(45, 2), Array( _
        46, 2), Array(47, 2), Array(48, 2), Array(49, 2), Array(50, 2), Array(51, 2), Array(52, 2), _
        Array(53, 2), Array(54, 2), Array(55, 2), Array(56, 2), Array(57, 2))


End Sub

Thanks for the assistance and helping guide me and learning something along the way.

Phil