Transponieren von Daten in Microsoft Excel

Sub BlockTranspose()
' -------------------------------------------------------
' BlockTranspose
' Transpose x/y/z-Array in Excel with VBA Macro
' www.mplx.eu, 04/04/2011
' ------------------------------------------------------- Config/Start
srcSheet = "Table1" ' Quell-Sheet
targetSheet = "Table2" ' Ziel-Sheet
MaxcntData = 100 ' letzte Zeile mit Daten (zeilenanzahl)
KeyColumn = 1 ' Spalte mit y-Bezeichnung (A=1, B=2, usw)
HeaderColumn = 2 ' Spalte mit x-Bezeichnung (A=1, B=2, usw)
DataColumn = 3 ' Spalte mit zu transponierenden Daten (A=1, B=2, usw)
HeadLine = 1 ' Kopfzeile
BlockCnt = 10 ' Anzahl Elemente (=Zeilen) pro Block
targetOffset = 0 ' TargetOffset (Zeilenanzahl)
' ------------------------------------------------------- Config/End

' Init
Set src = Worksheets(srcSheet)
Set target = Worksheets(targetSheet)

' Header
target.Cells(1, 1) = src.Cells(HeadLine, 1)
For cntHead = 1 To BlockCnt
target.Cells(1 + targetOffset, cntHead + HeadLine) = src.Cells(cntHead + HeadLine, HeaderColumn)
Next cntHead

' Transpose data
For cntData = 0 To (MaxcntData - 1) / BlockCnt
target.Cells(cntData + 1 + targetOffset, 1) = src.Cells(cntData * BlockCnt + 1, KeyColumn)
For cntHead = 1 To BlockCnt
target.Cells(cntData + HeadLine + 1 + targetOffset, cntHead + 1) = src.Cells(cntData * BlockCnt + cntHead + HeadLine, DataColumn)
Next cntHead
Next cntData

End Sub