VBA: moving an array but keeping the format unchanged

[kgkev]

I have a list of suppliers codes, most of them are in text format like

ABC, DEF, XYZ

however I have a few that are just numbers,

12456
6789
00056

This last one is causing me problems as the leading zeros are lost

I am moving the data, trying to avoid a copy paste:

Sheets("List").Range("A3:K1000").Value2 = Sheets("Query").Range("A3:K1000").Value2

what could be the best way of keeping the leading zeros?

 

[Fluff]

Try

Sheets("List").Range("A3:K1000").Value2 = Sheets("Query").Range("A3:K1000").[COLOR=#0000ff]Text[/COLOR]

 

[kgkev]

I tried a few variations of .value .value2 & .text

.text does not move any data across

 

[kgkev]

a bit of further testing

Sheets("List").Range("A4").Value2 = Sheets("Query").Range("A4").Text

works as expected, but

Sheets("List").Range("A4:K4").Value2 = Sheets("Query").Range("A4:K4").Text

doesn't move any data

 

[kgkev]

is anyone able to offer any further help on this one?

 

[RoryA]

You can't use Text on a multi-cell range (unless they all happen to contain the same displayed value).

Assuming the source cells actually contain the leading 0s rather than just being formatted to show them, you could set the target range number format to text:

With Sheets("List").Range("A3:K1000")
.Numberformat = "@"
.Value2 = Sheets("Query").Range("A3:K1000").Value2
end with

 

[kgkev]

Thank you very much - Worked perfectly.