macro to re-arrange data

I have been trying to write a macro to re-arrange the Cells in the rows and columns of Stock tables for the output I desire. Luckily the Stock Tables are generally the same each and every time (Different names and values), and the desired outcome is the same format..

Here is some example Data.

      A                        

1 Name

2 description

3 description

4 description

5 description

6 ID#: 56284

7 Quantity in stock: 34

8 Zoom In and开发者_StackOverflow中文版 Configure

      B

1 Name

2 description

3 description

4 description

5 description

6 ID#: 56284

7 Quantity in stock: 50

8 Zoom In and Configure

And I would like the Output to go into something like this(If possible to sheet2 starting on Cell B2):

B    C    E

B Being Row 1 C being Row 2 3 4 and 5 Combined E being JUST Row 7 Stock Value I.E 50

On a single spreadsheet there would be 4 columns, and 8 rows I would have to re-arrange.. Making 32 total.

It would be great to automated this, so any help would be greatly appreciated.

Let me clarify my understanding. For each column you want the following data format:

    A                                              A
1   Name                                       1   Name
2   Desc1                                      2   Desc1; Desc2; Desc3; Desc4
3   Desc2                      On sheet 2      3   50
4   Desc3                   --------------->
5   Desc4
6   Id#: 56284
7   Quantity in Stock: 50
8   Zoom in and configure

If this is the case you can use the following code. It assumes your data is in A1 to D8 in Sheet 1.

Sub FormatData()
    Dim col As Integer

    For col = 1 To 4
        With Worksheets(2)
           .Cells(1, col) = Cells(1, col) //Get name
           .Cells(2, col) = Cells(2, col) & "; " & Cells(3, col) & "; " & Cells(4, col) & "; " & Cells(5, col) //Concatenate descriptions into single string
           .Cells(3, col) = Cells(7, col) //Get quantity in stock
        End With
    Next col
End Sub