Do While (Current_cell(2, 1).Value <> "")
For lcv = 1 To 9
Current_cell(1, lcv).Value = Current_cell(2, 2).Value
Set Current_cell = Current_cell(2, 1)
Otherwise I would copy the column you want to transpose 9 times with spaces between the columns. Then auto fill numbers next to it so that each column has one catagory with increasing numbers next to it.
Column 1 would have a 1 next to the first site and a 2 next to the second site and so on. Column 2 would have a 1 next to the first Name and a 2 next to the second name and so on. Then I would sort each column by the numbers next to it and throw away anything below the numbers. After you delete the columns with numbers you will be left 9 columns, each representing one catagory and 700 rows.