Examples sheets:
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Count of Name</td><td style=";">Year of adm</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Salary</td><td style="text-align: right;;">2006</td><td style="text-align: right;;">2007</td><td style="text-align: right;;">2008</td><td style="text-align: right;;">2009</td><td style="text-align: right;;">2010</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";"> 10000-11999 </td><td style="text-align: right;;">3</td><td style="text-align: right;;">12</td><td style="text-align: right;;">13</td><td style="text-align: right;;">11</td><td style="text-align: right;;">14</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";"> 12000-13999 </td><td style="text-align: right;;">4</td><td style="text-align: right;;">10</td><td style="text-align: right;;">14</td><td style="text-align: right;;">8</td><td style="text-align: right;;">14</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";"> 14000-15999 </td><td style="text-align: right;;">2</td><td style="text-align: right;;">10</td><td style="text-align: right;;">16</td><td style="text-align: right;;">11</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";"> 16000-17999 </td><td style="text-align: right;;">7</td><td style="text-align: right;;">9</td><td style="text-align: right;;">13</td><td style="text-align: right;;">9</td><td style="text-align: right;;">12</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";"> 18000-19999 </td><td style="text-align: right;;">1</td><td style="text-align: right;;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;">16</td><td style="text-align: right;;">17</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";"> 20000-21999 </td><td style="text-align: right;;">4</td><td style="text-align: right;;">11</td><td style="text-align: right;;">14</td><td style="text-align: right;;">10</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";"> 22000-23999 </td><td style="text-align: right;;">3</td><td style="text-align: right;;">19</td><td style="text-align: right;;">9</td><td style="text-align: right;;">18</td><td style="text-align: right;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";"> 24000-25999 </td><td style="text-align: right;;">4</td><td style="text-align: right;;">8</td><td style="text-align: right;;">13</td><td style="text-align: right;;">12</td><td style="text-align: right;;">13</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";"> 26000-27999 </td><td style="text-align: right;;">2</td><td style="text-align: right;;">7</td><td style="text-align: right;;">12</td><td style="text-align: right;;">10</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";"> 28000-30000 </td><td style="text-align: right;;">1</td><td style="text-align: right;;">15</td><td style="text-align: right;;">14</td><td style="text-align: right;;">13</td><td style="text-align: right;;">11</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;"></td><td style=";">*******</td><td style=";">*******</td><td style=";">*******</td><td style=";">*******</td><td style=";">*******</td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">PTAU</p><br /><br /><b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Name</td><td style="font-weight: bold;;">AdmDate</td><td style="font-weight: bold;;">Salary</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Name43</td><td style="text-align: right;;">1/7/2009</td><td style="text-align: right;;"> $ 19,982 </td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Name24</td><td style="text-align: right;;">8/14/2007</td><td style="text-align: right;;"> $ 27,669 </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Name24</td><td style="text-align: right;;">5/12/2008</td><td style="text-align: right;;"> $ 16,545 </td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Name37</td><td style="text-align: right;;">4/15/2009</td><td style="text-align: right;;"> $ 25,992 </td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Name13</td><td style="text-align: right;;">5/27/2009</td><td style="text-align: right;;"> $ 26,648 </td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Name29</td><td style="text-align: right;;">9/2/2008</td><td style="text-align: right;;"> $ 10,253 </td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Name26</td><td style="text-align: right;;">12/30/2008</td><td style="text-align: right;;"> $ 15,837 </td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Name64</td><td style="text-align: right;;">12/26/2009</td><td style="text-align: right;;"> $ 28,603 </td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Name74</td><td style="text-align: right;;">12/10/2007</td><td style="text-align: right;;"> $ 20,386 </td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Name42</td><td style="text-align: right;;">7/11/2010</td><td style="text-align: right;;"> $ 28,261 </td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Name78</td><td style="text-align: right;;">5/2/2008</td><td style="text-align: right;;"> $ 18,574 </td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Name83</td><td style="text-align: right;;">9/27/2010</td><td style="text-align: right;;"> $ 26,372 </td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Name80</td><td style="text-align: right;;">6/12/2007</td><td style="text-align: right;;"> $ 28,152 </td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Data</p><br /><br />
Do the following:
Copy the code below.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(ActiveSheet.Columns("A:C"), Target) Is Nothing Then
Sheets("[COLOR=blue][B]PTAU[/B][/COLOR]").PivotTables(1).PivotCache.Refresh
End If
End Sub
Then, click the right mouse button on the sheet tab that contains the source data of the PivotTable (in my example – in the tab of the worksheet
Data) and choose
View Code.
In
Microsoft Visual Basic window that appears, paste the code you copied earlier.
Note: the name of the worksheet that containing the pivot table in my example is
PTAU, just change the name of your worksheet (that contains the PivotTable) to
PTUA also OR change this information in the code above (
PTUA to "
name of your worksheet").
Markmzz
