Excel VBA Question

[Andy Levy]

I have an excel table that is in a portrait fashion and i would like t o
change it to landscape. Im sure i can do it using VBA but not sure how -
any ideas will be greatly appreciated.

Existing Table Example.

Heading 1 - Value 1
Heading 2 - Value 2
Heading 3 - Value 3
Heading 4 - Value 4
Heading 5 - Value 5

How i want it to be

Heading 1 Heading 2 Heading 3 Heading 4 Heading 5
Value1 Value 2 Value 3 Value 4 Value 5

I need to transfer the column values.

Many many thanks in advance

AL

 

[Mike Barton]

Andy,

You can automate the Transpose PasteSpecial option as in the following code.

The only annoyance, in terms of OOP, is that you need to select the target
range (just the first cell) before using the PasteSpecial method.

Dim rngSourceRange As Range, rngTargetRangeFirstCell As Range

Set rngSourceRange = Range("A15") 'Table to be transposed
Set rngTargetRangeFirstCell = Range("A7") 'First cell where transposed
table to appear

rngSourceRange.Copy
rngTargetRangeFirstCell.Select
rngTargetRangeFirstCell.PasteSpecial Transpose:=True

Regards,

Mike Barton