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

[TABLE="width: 418"]
<tbody>[TR]
[TD]Job #[/TD]
[TD]Job Name[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

SHEET 2 - Job# and Job Name to be listed on cell A1 and A2. Data list to start on cell B1[TABLE="width: 453"]
<tbody>[TR]
[TD]Job #[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Job Name[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
</tbody>[/TABLE]


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