Public sub repeated with one variable criteria

[BH]

Hi

Hope this makes sense! guidance would be much appreciated....

I send out a monthly report that is sent to 21 different organisations. All of the 21 organisations info is held in one excel spreadsheet and i use a pivot table and dropdown box to filter the information. The next procedure is to copy and paste this information into individual packs (Word)

I am in the process of automating the process and have wrote a macro which works fine. The issue I have is that I don't really want to duplicate the code 21 times as all i am changing is the dropdown box selection to pick the next organisation. From the code below can any one help me by showing me how to change the code in just one area? The organisation that changes is shown as [CENTRAL NNEC]. I am assuming that calling a public sub is the way to go???


ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[PRACTICE].[PRACTICE].[PCT]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[PRACTICE].[PRACTICE].[Cluster]").VisibleItemsList = Array("")
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[PRACTICE].[PRACTICE].[Practice]").VisibleItemsList = Array( _
"[PRACTICE].[PRACTICE].[Practice].&[CENTRAL NNEC]")


Thanks for your help

 

[UnsolvedCoding]

Can't you just use a for each loop pertaining to the items in the drop down box?

 

[BH]

Hi

I am struggling with that as the information comes from a huge data set accessed by many different areas. I want to access just 21 of possibly 200 areas. Is there a way I can loop but only pick out the ones I require?

I am not an expert in this area so any assistance would be gratefully received.

 

[SkipVought]

Make a list of your area values.

Loop thru this list, using the value as the pivot item value. I usually prefer to use a PAGE/FILTER PivotField, than a ROW PivotField for this kind of application.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BH]

Good idea Skip, i will give it a go....thanks