Macro para copiar datos de varias carpetas y unirlo a un solo excel

Tengo varios archivos de excel en varias carpetas, cada carpeta tiene una cantidad diferente de archivos pero todos tienen el mismo formato.

Quiero unificar toda esa información en una sola base de datos, pero a su vez los datos de esos libros que quiero capturar luego quiero que sean pegados transpuestos y con los valores.

Sé que con una macro se puede copiar data de otros documentos y pegarla pero no sé como transponerla y que lea solo la que quiero.

Adjunto los dos archivos el antes y el despues.