Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to change matrix to list for Pivot table Analysis
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code to change matrix to list for Pivot table Analysis
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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change pivot table in code | Excel Programming | |||
#Value in Analysis Services Pivot Table | Excel Discussion (Misc queries) | |||
Pivot Table Page Change Code | Excel Discussion (Misc queries) | |||
Pivot table and customer analysis | Excel Worksheet Functions | |||
Pivot Table analysis | Excel Programming |