How to avoid removing fill effects and vertical alignment when unlisting a table?

[CRHAAG]

I noticed a strange thing when using VBA with tables yesterday. My code is basically doing the following:

Copy a table from sheet A
pasting thta table to sheet B
using .unlist to convert the table to a range

The issue is that the top row of the table has vertical text alignment and a gradient fill. When I convert it to a range, the alignment goes back to horizontal and the fill gets removed. This doesn't happen if I use a solid fill color.

Is there any way to stop it from doing this, or do I need to write code to go bad and change things to how I want them?

 

[GraH]

Hi,

The idea might be completely off but about this step: "pasting the table to sheet B" -> Since you convert to a range, why not paste values and number formats? Then format/colour cells as you need.