Paste one Array with and keep destination formatting

[drom]

Hi and Thanks in advance!


Say I have a range in a book,

range("C5:Z1540") 'just for this eg
If I do create an array like:

dim aMyArray ():   aMyArray= range("C5:Z1540").value

If I go to other Workbook using VBA, and I do paste the array, like:

    Activesheet.Cells(1, 1).Resize(UBound(MyArray, 1), UBound(MyArray, 2)) = MyArray

The Activesheet does not keep the formatting, the Activesheet gets new formats

Is it possible to paste one array, somewhere but with the destination formats ??
I mean not changing the destination formats​

ps:
I do know how to copy and paste values.
I know how to change cells formatting.
I know how to move/copy a sheet to a new book

I do not want to do so​

I am trying to know if paste as values is possible when pasting one array

 

[Chihiro]

Upload sample workbook with code, where you can demonstrate your issue.

By default Array does not transfer format. Only values.
You can notice this by putting range with number formatted as text. Then putting it into new range. It will default to numeric format.

Edit: FYI - There are exceptions to above rule, those are DateTime, Currency etc. These retain displayed values (along with underlying), this is due to special data type assigned to those values. If you want true value only (underlying Double type), then use .Value2 instead of .Value.