Diagonals on tables transposed to columns | Excel forum

Hi Ben, thanks for the compliment! Here's the transposition code - you'll need to adjust the worksheet names if they are not "Sheet1" and "Sheet2" for source and destination data. One other thing, if you run this directly on a Pivot table you may need to disable the GetPivotData functionality. Let me know if you need help with this.

Here's the code:

*****

Option Explicit

Sub transpose_me()

Dim intTargetRowCount As Integer
Dim inttargetcolumncount As Integer
Dim diagonal As Integer
Dim shuntdown As Integer
Dim across As Integer

'set initial coordinates for first straightened diagonal

intTargetRowCount = 1
inttargetcolumncount = 1

'determine the number of diagonals

For diagonal = 1 To Sheets("sheet1").Range("a1").CurrentRegion.Columns.Count

shuntdown = 1

For across = diagonal To Sheets("Sheet1").Range("a1").CurrentRegion.Columns.Count

Sheets("sheet2").Cells(intTargetRowCount, inttargetcolumncount) = Cells(shuntdown, across + 1)
intTargetRowCount = intTargetRowCount + 1 'move down a row on destination sheet
shuntdown = shuntdown + 1 'increment the row number on source sheet

Next across 'move to next column

'reset coordinates for new straightened diagonal

inttargetcolumncount = inttargetcolumncount + 1
intTargetRowCount = 1

'move onto the next diagonal

Next diagonal


End Sub


*****

Hope this helps,

Anthony