VBA transpose multiple times

Hi,

I am currently working on a big set of data downloaded from Morningstar Direct. I need help with a macro that can change the exported document from horizontal to vertical.

For example:

From:
Ticker Data1-2000 Data1-2001 Data1-2002 ... Data1-2016 Data2-2000 Data2-2001 and so on


To:
Ticker Data1-2000
Data1-2001
...
Data1-2016
Data2-2000
Data2-2001
...

Every datavariable goes from 2000 to 2016. We have more then 500 tickers that need the same transformation. Is there any VBA code that can do this for me? It will save my life (at least my Easter)!


Many thanks in advance!!

Hi Kaper,

Thanks for the quick and informative reply. This is where i am at now:

I have made this macro:

Sub Flip()
'
' Flip Macro
'

'

Sheets("S&P 500 Constituents").Select
Range("I2:X2").Select
Selection.Copy
Sheets("Sheet1").Select
Range("D2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("S&P 500 Constituents").Select
Range("AB2:AQ2").Select
Selection.Copy
Sheets("Sheet1").Select
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("S&P 500 Constituents").Select
Range("AR2:BG2").Select
Selection.Copy
Sheets("Sheet1").Select
Range("F2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("S&P 500 Constituents").Select
Range("BH2:BW2").Select
Selection.Copy
Sheets("Sheet1").Select
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("S&P 500 Constituents").Select
Range("BX2:CM2").Select
Selection.Copy
Sheets("Sheet1").Select
Range("H2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

End Sub


As you can see everything is taken from row 2 in my S&P500 sheet, and then transposed into sheet1.

How can i make this macro repeat the same operation on row 3, 4, 5 ... 518?


Thank you very much for the reply!