Macro to Select Pivot Table Fields Based Upon Other Pivot Tables

[Tarver]

Hello, team!

I've got a time-consuming task I'm trying to streamline. I have a spreadsheet with four pivot tables on one sheet, each pulling different information about the same Merchant. I filter by the Merchant field.

This data comes from a large external database, and if I record a macro, it doesn't show the Merchant name, but a number (and this number has no meaning for me. I can't query the table it's coming from.)

I'd like to manually select the first Merchant, then have the other three pivot tables on the sheet be updated via the macro to select the same merchant for all of the other tables.

This is the code generated when I select a Merchant from the first table:
ActiveSheet.PivotTables("PivotTable20").PivotFields( _
"[Merchant].[Merchants].[Level 02]").VisibleItemsList = Array( _
"[Merchant].[Merchants].&[3680]")
ActiveSheet.PivotTables("PivotTable20").PivotFields( _
"[Merchant].[Merchants].[Level 03]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("PivotTable20").PivotFields( _
"[Merchant].[Merchants].[Level 04]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("PivotTable20").PivotFields( _
"[Merchant].[Merchants].[Level 05]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("PivotTable20").PivotFields( _
"[Merchant].[Merchants].[Level 06]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("PivotTable20").PivotFields( _
"[Merchant].[Merchants].[Level 07]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("PivotTable20").PivotFields( _
"[Merchant].[Merchants].[Level 08]").VisibleItemsList = Array("")

I believe I need to capture that first item (which should be 3680 in the code above) and then insert it into the other four tables, but I'm not sure how to do that.

Any help would be most appreciated!

Thanks!

BT

 

[TDCox]

Hi @Tarver

Leaving aside the capture of the first item for the moment, how many merchants are in the external database, and how many do you interact with whenever you run this? E.g. there are 10,000 merchants and on a given week you pull through details for 20 merchants; the next week you pull through 10 of the same merchants and 15 new ones.

 

[Tarver]

Yes, you've pretty much summed it up. There are thousands of merchants, but I work with the top 50 or so 80% of the time, and the top 20% 95% of the time.

 

[TDCox]

Hi @Tarver

I sent this before but it appears to have disappeared for some reason.

Is there any reason why you couldn't create a lookup table within your spreadsheet for the merchants name and ID? E.g. a separate field that you selected the name from and it returned the ID, with the pivot tables then updated from that field via a macro. Assuming the merchant's name is pulled through you should be able to do a do a For each loop in vba and extract the name and ID to put on your separate sheet, even if you can't directly query the external table. Of course the easier way would be to pivot the merchant name and ID and then cut and paste.