Pivot Table VBA - Macro to select header rows based on cells below them

Data Science

New Member
Joined
Nov 7, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I'm currently working on a project at work where I want to separate out customers that have purchased specific products. I have 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 sample data screenshot is an 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.

Sample Data - Screenshot small.jpg


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 select or 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 column or a new 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.

Thank you all in advance!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top