Copy cell format with visual basic

[Exceler]

I'm using the following to copy (and transpose) values from one sheet to another:

For Each c In Worksheets("Data").Range(ActiveCell.Row & ":" & ActiveCell.Row)

Worksheets("New").Cells(c.Column, 1).Value = Worksheets("Data").Cells(2, c.Column).Value

Next c

Works fine with copying the values, but does not copy the formats. Any way to copy the formats without selecting each sheet and using ActiveSheet.paste on each cell? Some of the values include strings with embedded formats (like one word being underlined). Thanks

[mikerickson]

This will copy the values and formats from myRange, transpose them and put them (with the same upper-left cell address) on Sheet2. You will have to adapt it to your situation. (DO NOT change the "A1" in the With.. statement, it refers to the upper-left cell of the copy-to range not of the spreadsheet.)

Please Login or Register  to view this content.

[Exceler]

Thanks for the reply. This works for all formats except embedded ones like in Cell N2 on Sheet "Data" of the attached.

This spreadsheet is intended to allow flexibility in adding fields ad hoc, so with the Dim and Set commands I would have to redefine fixed range addresses each time it is used. Are the Dim and Set commands necessary to get embedded formats to copy?

To run the program, right-click on any non-blank value on Line 3 of Sheet "Data".

Thanks

[mikerickson]

Set is used like Let when dealing with Objects (like Ranges) rather than values. It is required.

You could use Re-Dim if your arrays are declared correctly

Please Login or Register  to view this content.

There is also a ReDim Preserve that doesn't null out the old values

Please Login or Register  to view this content.

leaves myCells(1)=Range("A1"). If you just ReDim myCells(1 to 3), myCells(1) would be Nothing


Or you could use named ranges rather than range variables to keep track of your dynamic ranges.