В Эксель есть встроенные функции, предназначенные для этого.
Предлагаю ещё один, свой вариант макроса. Надеюсь, что будет работать без сбоев.
Основной макрос – Транспонирование таблицы.
В файле 2 листа. Лист результат при загрузке файла удаляется каждый раз.
Исходную таблицу надо располагать на одноименном листе.