Macro to Copy Data from a Pivot Table

Hi, I have a pivot table, but need to make a sort of top issues version of it.

The table has 4 areas, A, B, C and D.

Within this areas it has defect places and defect types.

Defect places are things like door, floor.
Defect types are things like scratch, mark etc.

The data shows the total numbers of theses defects for each week.


I need a summary table showing the top 10 defect places in A, with the top 5 defect types within those defect places. Along with the total number of defects in that defect place (not just the total of the top 5 defect types).

I can copy and paste the info out but trying to do a macro it obviously uses cell references, so if the data changes position then it wouldn't work.

For example in area A. The pivot table has the defect places sorted with the place with the most defects at the top. And the defect types within that defect place sorted descending aswell.

Copying the data works fine, but if the data changes and a different defect place becomes top, then it won't have the same number of possible defect types so the cells move around.


Is there a way I can tell it to look down a column for something, then use that as a reference for copying data?

For example look down column B until you see a defect place. Then copy the data from columns C-F in that row and the 4 rows below it.

Then look down column B until you see the total for that defect place, then copy the data from columns C-F in that row.



The destination cells should always be the same positions as it only shows top 10s and top 5s.


Hopefully this makes sense. If not I'll try and create an example, I can't really put the real data on the interenet 'cause it's a bit secret and a very big file!

Hi

I think you will have to go down the example path.....

rylo