Copying cell formatting using VBA

[petereddy]

Hi,

Two questions about copying ranges:

First, the command

Range(Cells(1, 1), Cells(1, 3)) = Range(Cells(2, 1), Cells(2, 3))

seems to do nothing, but transposing the range twice, as done here

Range(Cells(1, 1), Cells(1, 3)) = Application.Transpose(Application.Transpose(Range(Cells(2, 1), Cells(2, 3))))

does what I expected the first line to do. Any thoughts how to tighten up the code?

Second, is there a simple way to copy the NumberFormat of each cell, similar to how the second line above copies the values? For example if the NumberFormat in A2 is "0%", in B2 is "#,##0.00" and in C2 is "mmmm d, yyyy", is there a way to apply that to, say, A1:C1? Right now I'm looping through each cell in the range to get its NumberFormat, and applying it to the corresponding cell. Is that my best bet? Thanks.

 

[Fluff]

The part in red does what you're code is trying to do & the part in blue copies the formats & values

   Range("A1:C1").Value = Range("A2:C2").Value
   
   Range("A2:C2").Copy
   Range("A1:C1").PasteSpecial xlValues
   Range("A1:C1").PasteSpecial xlFormats

 

[petereddy]

Thanks. No way to copy the formatting without using the clipboard?

 

[Fluff]

Yes,

  Range("A2:C2").Copy Range("A1")

but that copies everything.

 

[petereddy]

That seems to still use the clipboard. Regardless, thanks for engaging.

 

[Fluff]

AFAIK it by-passes the clipboard & copies the cells directly.