Paste one array does not take desired Format

[drom]

Hi and thanks in advance!

I have one array with with many rows and Columns, populated using a Macro

when I have to paste this array in a sheet.

I get the Values OK, but the formatting of the pasted cells do not show the values the way I was expecting.

I mean: say

Sheets(1).Cells(5, 1).Resize(xRows, xCols) = aData
xRows is equal to UBound(aData, 1)
xCols is equal to UBound(aData, 2)

but if aData(2,5) is equal to 106201700000036, because this value comes from a sheet("XX").range("C25000") and this cell shows the full value and is formatted as Text
Sheets(1).Cells(3, 6) shows 1,06202E+14, despite this Column is formatted as well as Text

is there any way of avoiding this ?

ps: the Pasted Place is formatted the same way the origin cells,
so when I need Text comes and goes to columns formatted as Text
When I need Value comes and goes from Columns formatted as values

Thanks!

 

[Rick Rothstein]

Hi and thanks in advance!

I have one array with with many rows and Columns, populated using a Macro

when I have to paste this array in a sheet.

I get the Values OK, but the formatting of the pasted cells do not show the values the way I was expecting.

I mean: say

Sheets(1).Cells(5, 1).Resize(xRows, xCols) = aData
xRows is equal to UBound(aData, 1)
xCols is equal to UBound(aData, 2)

but if aData(2,5) is equal to 106201700000036, because this value comes from a sheet("XX").range("C25000") and this cell shows the full value and is formatted as Text
Sheets(1).Cells(3, 6) shows 1,06202E+14, despite this Column is formatted as well as Text

is there any way of avoiding this ?

ps: the Pasted Place is formatted the same way the origin cells,
so when I need Text comes and goes to columns formatted as Text
When I need Value comes and goes from Columns formatted as values

Try it this way...

Sheet("XX").Range("C25000").Copy Sheets(1).Cells(5, 1)

 

[drom]

not Possible.
The array is populated from hundreds of sheets, with dinamic ranges, tables etc...
This is why i need to know if there is any way, other than using at the end of the Macro text to columns etc...
The Pasted Range could have 21000 rows