Crear listas a partir de tablas (MS Excel)

Con a siguiente macro podemos crear listas a partir de tablas en excel. Con estas listas podemos luego crear tablas dinámicas.


Sub flat_table()

' Para crear listas a partir de tablas.
' Fuente: Macro for converting Crosstab data into a flat table
' http://www.ozgrid.com/forum/showthread.php?t=45511

Dim wsData As Worksheet
Dim wsNew As Worksheet
Dim rngSrc As Range
Dim rngDst As Range
Dim LastCol As Long
Dim LastRow As Long
Dim LastRowDst As Long
Dim I As Long

Set wsData = Worksheets(ActiveSheet.Name)
Set wsNew = Worksheets.Add

LastRow = wsData.Range("A" & Rows.Count).End(xlUp).Row
LastRowDst = 2
LastCol = wsData.Range("IV1").End(xlToLeft).Column

wsNew.Range("A1:C1") = Array("Fila", "Columna", "Valor")
For I = 2 To LastRow
Set rngSrc = wsData.Range("A" & I)
Set rngDst = wsNew.Range("A" & LastRowDst)

rngSrc.Copy rngDst.Resize(LastCol - 1)

rngSrc.Offset(-(I - 1), 1).Resize(, LastCol - 1).Copy
rngDst.Offset(0, 1).PasteSpecial Transpose:=True
rngSrc.Offset(, 1).Resize(, LastCol - 1).Copy
rngDst.Offset(0, 2).PasteSpecial Transpose:=True
LastRowDst = LastRowDst + (LastCol - 1)
Next I
End Sub