Macro to extract and transpose data from multiple tabs into a single sheet

[thatexcelguy91]

Hi,

I have a workbook that contains data for ~200 companies in different tabs in the same format. The format has all the years in a single row at the top, with multiple columns eg 2010,2011...2016. Also there are metrics listed in single column (first one,extreme left) eg revenue, cost etc in a single column in different rows.

I intend to create a macro which can create a flat file structured tab which essentially aggregates data from all the tabs into a single tab. The format should be such that the metrics and years are transposed. Eg the metrics are listed at the top in a single row,multiple columns. Then all the companies are shown from the latest year, with 2016 year shown for all the companies first, then 2015 for all...and so on.

So far have only been able to create the below, any help would be great:

Sub CreateFlatFile()
Dim wb As Workbook, sh As Worksheet, shname As String, i As Integer
i = 251
For Each sh In ActiveWorkbook.Worksheets
If sh.Name Like "Company 1*" Then
sh.Activate
Cells(i, 21).Resize(1, 300).Copy
Worksheets("DataforTableau").Select
Cells(2, 21).PasteSpecial Transpose:=True
End If
Next sh
End Sub