Re: Consolidate Several Crosstabs into single flat table
SOLVED! Thanks. The problem I had is that I was using the second sheet with one less row field.
For anybody interested in the final version is this one. I also attached the file as an example.
Thanks for your help MrRedli
Option Explicit Sub CreateFlatTable() 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 Dim x As Long Set wsNew = Sheets("Consolidated") 'Clears Consolidated Sheet Cells.Select Selection.ClearContents Range("A1").Select '---- wsNew.Range("A1:E1") = Array("CECO", "Localidad", "MainAcc", "Fecha", "Monto") LastRowDst = 2 For x = 2 To Sheets.Count Set wsData = Sheets(x) LastRow = wsData.Range("A" & Rows.Count).End(xlUp).Row LastCol = wsData.Range("IV1").End(xlToLeft).Column For i = 2 To LastRow Set rngSrc = wsData.Range("A" & i & ":C" & i) Set rngDst = wsNew.Range("A" & LastRowDst) rngSrc.Copy rngDst.Resize(LastCol - 3) rngSrc.Offset(-(i - 1), 3).Resize(, LastCol - 3).Copy rngDst.Offset(0, 3).PasteSpecial Transpose:=True rngSrc.Offset(, 3).Resize(, LastCol - 3).Copy rngDst.Offset(0, 4).PasteSpecial Transpose:=True LastRowDst = LastRowDst + (LastCol - 3) Next i Next x End Sub