I'm currently working on a project at work where I want to separate out customers that have purchased specific products. I have Excel sales reports that show the customer's name, what products they purchased, how much they paid, etc. I have a macro built that organizes the report and builds a pivot table off of the resulting data. The attached Excel file is for example only. It is considerably smaller than the actual customer report that I am working with. My actual report has thousands of customers and 100k+ products. For privacy reasons I have taken out any real customer names or purchase data, and replaced it with generic data.
I can use conditional formatting to highlight any cells in the pivot table that contain a specific product name. What I want to do next is set up a macro that will find the highlighted cells (or the product name), and then highlight the header row that the data cell "belongs to". Eventually I want to use these highlights to filter out any customers that have purchased a specific product, and copy that customers data into a different sheet. By "header row" I am referencing the subtotal row what shows the customer's name and the totals of each column. At the end of the macro, I want a list of customers that have purchased a specific product to be copied over to a different sheet separate from the original data.
Is there any way for a VBA macro to find a specific cell (either by name or by highlight color) in a pivot table, and then highlight the subtotal / header row that the data cell belongs to? I can't seem to find any VBA commands referenced online that understand the relationship between a data cell in a pivot table, and the subtotal / header row above it.
For example, in the attached file I have highlighted all instances where a customer bought a football. I need a macro that will take all customers who bought a football (customers A, B, D, and G) and copy their data from that pivot table into a separate sheet. Ideally if I could move all of the other rows beneath that header row as well that would be great. However, at this point if I can just copy the customer header row out, that would be enough.
