Vertical to Horizontal Data using a VBA - Please Help!

[excel103]

Hello, I need help moving vertical data from Sheet 1 to horizontal on Sheet 2 thru a macro only, not a formula or copy and paste. Please see below.

SHEET 1
Due to headers on Sheet 1, data list starts at Cell A9 and currently ends at cell A150

Job #	Job Name
1	A
2	B
3	C
4	D
5	E

<tbody>
</tbody>

SHEET 2 - Job# and Job Name to be listed on cell A1 and A2. Data list to start on cell B1

Job #	1	2	3	4	5
Job Name	A	B	C	D	E

<tbody>
</tbody>

Thank you.

 

[Fluff]

How about

Sub TransData()
   Dim ary As Variant
   ary = Sheets("Sheet1").Range("A9:B150")
   Sheets("sheet2").Range("b1").Resize(UBound(ary, 2), UBound(ary)).Value = Application.Transpose(ary)
End Sub

 

[excel103]

Fluff,

I was able to resolve the adding of a column in between project types. I would like to extend the formatting from Sheet 1 (fill color and bold) to sheet 2. is that possible?

 

[Fluff]

Do all cells have the same formatting?

 

[excel103]

No. The cells on Sheet 1 do not share the same formatting. However, could we add to that macro to fill each cell on Sheet 2 color grey and font on the cells to be bold?

 

[Fluff]

How about

Sub TransData()
   Dim ary As Variant
   ary = Sheets("sheet1").Range("A9:B150")
   With Sheets("sheet2").Range("B1").Resize(UBound(ary, 2), UBound(ary))
      .Value = Application.Transpose(ary)
      .Interior.Color = 10921638
      .Font.Bold = True
   End With
End Sub

 

[excel103]

It worked perfectly! Just what I needed. Thank you very much!!!

 

[Fluff]

Glad to help & thanks for the feedback