VBA code to change matrix to list for Pivot table Analysis

[Martin]

I would like to convert a "budget spreadsheet" in Matrix form to a list for
use as a data Source for a Pivot Table. The matrix has a total of 16
columns, 4 columns define the information in the row (Ledger Code,
Description, etc.) and then 12 columns containing the data for the periods
(January - December).

What would be the best way to do this without lot's of cutting/pasting and
Paste Special Transpose?

Thanks for any help

 

[Patrick Molloy]

try this:


Option Explicit
Sub Rebuilder()
'cols A-D are static
'cols E - P are months
' headers are row 6
' data is in 45 rows below headers

Dim mnth As Long
Const blockrows As Long = 45
Const headerrow As Long = 6
For mnth = 1 To 12 '(1 - 12 months + 4 for the column offset

'copy static block
With Cells(headerrow + blockrows * mnth + 1, 1).Resize(blockrows, 4)
.Value = _
Range(Cells(headerrow + 1, 1), Cells(headerrow + blockrows + 1,
4)).Value
End With
'copy month name
With Cells(headerrow + blockrows * mnth + 1, 5)
.Resize(blockrows, 1).Value = _
Cells(headerrow, 4 + mnth).Resize(, 1).Value
End With

'copy month data
With Cells(headerrow + blockrows * mnth + 1, 6)
.Resize(blockrows, 1).Value = _
Cells(headerrow + 1, 4 + mnth).Resize(blockrows).Value
End With


Next



End Sub

 

[martin.a]

Thanks

Thanks Patrick the script worked great, a real help.

Cheers, Martin