I need a vba solution to create a report using 2 data sources, a spreadsheet and a pivot table. I've attached sample data
in the attachment.
I have a 3rd summary sheet "Allocation" with headings (refer attachment)
In the worksheet "Split" I have a spreadsheet listing a bunch of cost centres (Col. A & B) and 4 reporting segments. They
are represented by C1, D1, E1 & F1. The rest of the table contains percentage values by reporting segment for each of my
cost centres.
In the worksheet "Pivot" I have a pivot table with ledger codes (Col A), the same 4 reporting segments, and a corresponding
value for each ledger code per reporting segment.
I need to do the following:
Clear the contents of the "Allocation" worksheet (retain rows 1 & 2 and formatting). Starting at A3, G3, M3 & S3
respectively, copy:
(a) from the pivot table, for each segment, copy to another worksheet ("Allocation") the ledger code (col A) and it's
value (col B) where the value in column B is greater than zero. Copy the value of the pivot table code in col A for
each of the segments in the "Allocation" sheet in B3, H3, N3 & T3
(b) then for each ledger code copied I need to create duplicate rows equal to the number of cost center codes in "Split"
where the value in the segment column is not zero. The cost centre codes in col A & B should be copied to the sheet.
The "Allocation" sheet is a representation of what the resulting report should look like.
I have been researching how to do this, but cannot find anything useful. Would appreciate if someone can point me in the
right direction to start this project.
Reply With Quote