title: Add Multiple Values to Multiple Pivots
body: <p>I'm a novice and haven't been able to piece together anything I've searched for into a solution. Any help is appreciated.</p>
  <h2>Data Set</h2>
  <p>I have a large number of pivot tables in a worksheet. Each of the pivot tables is sourced by a single very large table (over 1,000 columns and thousands of rows). Each pivot table provides a specific insight.</p>
  <p>This data set is unfortunately poorly designed which I have no control over and limits my approach.</p>
  <h2>Objective</h2>
  <p>I would like to refresh the values in the pivot tables as needed. Each pivot table may have a few values to add, while some pivots may have dozens of values to add.</p>
  <p>I'm trying to avoid searching for and adding multiple data elements in the pivot table field listing containing well over a thousand elements and doing that for dozens of pivot tables each time this report needs to be refreshed.</p>
  <p>An example of what I'm looking for is below. The good news is that the list of values that I need in each individual pivot is set in a named range. So for the table below, I have a named range &quot;Pivot1_List&quot; that includes [Value3,Value5,Value6]. There are named ranges for each of the pivots I need to populate.</p>
  <h2>Example</h2>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Pivot1</th>
  <th>Pivot2</th>
  <th>Pivot3</th>
  <th>Pivot4</th>
  <th>Pivot5</th>
  <th>Pivot6</th>
  <th>Pivot7</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Value3</td>
  <td>Value2</td>
  <td>Value1</td>
  <td>Value17</td>
  <td>Value421</td>
  <td>Value81</td>
  <td>Value101</td>
  </tr>
  <tr>
  <td>Value5</td>
  <td>Value10</td>
  <td>Value51</td>
  <td></td>
  <td>Value422</td>
  <td>Value84</td>
  <td>Value102</td>
  </tr>
  <tr>
  <td>Value6</td>
  <td>Value11</td>
  <td>Value52</td>
  <td></td>
  <td>Value423</td>
  <td>Value85</td>
  <td>Value103</td>
  </tr>
  <tr>
  <td></td>
  <td>Value23</td>
  <td>Value53</td>
  <td></td>
  <td>Value424</td>
  <td></td>
  <td>Value104</td>
  </tr>
  <tr>
  <td></td>
  <td>Value24</td>
  <td>Value54</td>
  <td></td>
  <td>Value425</td>
  <td></td>
  <td>Value105</td>
  </tr>
  <tr>
  <td></td>
  <td>Value25</td>
  <td>Value55</td>
  <td></td>
  <td></td>
  <td></td>
  <td>Value1001</td>
  </tr>
  <tr>
  <td></td>
  <td></td>
  <td>Value56</td>
  <td></td>
  <td></td>
  <td></td>
  <td>Value1002</td>
  </tr>
  </tbody>
  </table>
  </div><h2>Solutions Tried</h2>
  <p>I mentioned I'm a novice so I haven't gotten very far with anything. I've tried to add the name of the named range above the pivot to use as a reference. I don't know enough about PowerPivot to know if that's a viable solution. I initially thought perhaps not given that the data set each pivot references is the same.</p>
  <p>This is probably an ineffective approach. I'm not sure how to reference the name of the named range that's populated above each pivot. I figure offset will work, but I can't get it to work. And I'm not sure how to reference the values for each named range.</p>
  <pre><code>Dim s as Worksheet
  Dim pt as PivotTable
  Dim pf as PivotField
  
  Set s = ActiveSheet
  For each pt in s
  Then
  pt.AddDataField.'not sure how to add named range values'
  Exit For
  </code></pre>
  <p>I hope this isn't too confusing.</p>
  <p>Thanks for any help!</p>