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]

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.

Don't confuse lengthy with meaning it is not elegant! That is exactly how you want to do it. That is how Text to Columns works. If you leave off the Array values, it will default all fields to General.
I see that you changed them all to 2 "Text". That is fine, if you want all the fields to be "Text". Note that they can all be different (so you have some General, some Text, some Date, some "Do Not Import").

There is one minor thing you can do to shorten up your code a little. Whenever you have one line that ends in ".Select" and the next line starts with "Selection" or "ActiveCell", you can usually combine those lines (it isn't necessary to select ranges to work with them, and it actually speeds up your code to eliminate them).

So you can combine these two lines:

Range("b7:bf6000").Select
Selection.NumberFormat = "@"

to this single line:

Range("b7:bf6000").NumberFormat = "@"

 

[philwojo]

Thanks, I made that change and it is working as expected, again I appreciate the extra knowledge and it makes sense.

 

[Joe4]

You are welcome!