How do I loop thru a data range in a pivot table

Question

I have two pivot tables.  I want to use VBA to extract the data displayed on each table and put it on another sheet, to make a report.   

How do I do that.

Basically, I want to loop thru the data on each, and when there are differences (both pivot tables have the exact same filters) I want to put the differences, including deleted and added items on a new sheet.

Answer 1

Slightly confused by the tables you show.

If they are pivot tables I am surprised there are multiple rows for the same combinations e.g. B/Red and B/Yellow in the first table, and they don't appear to be sorted in the way you described.

The problem also seems to have changed, with colour as an additional row field.

The "changes" table will presumably also have to have a colour column and you will need a row per product/colour combination from either table. 

The GETPIVOTDATA formulas I suggested will need an additional pair of arguments for "Colour" and the colour cell on the row in question.

I'm struggling to have time to help you - being on vacation for a few days.

You're right that you can't upload a file to this forum.

You can provide a link to a file on a file-sharing website such as OneDrive or Dropbox.

That would make it easier to help you develop the solution.

One idea you might consider is creating a 3rd pivot table based on consolidating the other 2.

That will give you the combinations of product and colour from either table in a single range.

You could then copy the row labels to a new worksheet and build the table using my formulas to access the two original pivot tables; the third pivot table could then be removed.