Text/Data in columns to a pivot table

[divo9000]

Using this data, I am trying to use Excel VBA to create the following two worksheets. Can anyone help? Needs to be created in Excel

Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Program</td><td style="font-weight: bold;;">Version</td><td style="font-weight: bold;;">Tab</td><td style="font-weight: bold;;">Question #</td><td style="font-weight: bold;;">Value</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style=";">P1</td><td style=";">V1</td><td style=";">Tab 1</td><td style=";">Question 1</td><td style=";">
</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">P1</td><td style=";">V1</td><td style=";">Tab 1</td><td style=";">Question 2</td><td style="text-align: right;;">1</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">P1</td><td style=";">V1</td><td style=";">Tab 1</td><td style=";">Question 3</td><td style=";">
</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style=";">P1</td><td style=";">V1</td><td style=";">Tab 1</td><td style=";">Question 4</td><td style="text-align: right;;">8</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style=";">P1</td><td style=";">V1</td><td style=";">Tab 1</td><td style=";">Question 5</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style=";">P1</td><td style=";">V1</td><td style=";">Tab 2</td><td style=";">Question 1</td><td style=";">P23</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style=";">P1</td><td style=";">V1</td><td style=";">Tab 2</td><td style=";">Question 2</td><td style=";">P73</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style=";">P1</td><td style=";">V1</td><td style=";">Tab 2</td><td style=";">Question 3</td><td style=";">P5</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style=";">P1</td><td style=";">V1</td><td style=";">Tab 3</td><td style=";">Question 1</td><td style=";">
</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style=";">P1</td><td style=";">V1</td><td style=";">Tab 3</td><td style=";">Question 2</td><td style="text-align: right;;">4</td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style=";">P1</td><td style=";">V1</td><td style=";">Tab 3</td><td style=";">Question 3</td><td style=";">
</td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style=";">P1</td><td style=";">V2</td><td style=";">Tab 1</td><td style=";">Question 1</td><td style=";">
</td></tr><tr><td style="color: #161120;text-align: center;">14</td><td style=";">P1</td><td style=";">V2</td><td style=";">Tab 1</td><td style=";">Question 2</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">15</td><td style=";">P1</td><td style=";">V2</td><td style=";">Tab 1</td><td style=";">Question 3</td><td style="text-align: right;;">2</td></tr><tr><td style="color: #161120;text-align: center;">16</td><td style=";">P1</td><td style=";">V2</td><td style=";">Tab 1</td><td style=";">Question 4</td><td style="text-align: right;;">6</td></tr><tr><td style="color: #161120;text-align: center;">17</td><td style=";">P1</td><td style=";">V2</td><td style=";">Tab 1</td><td style=";">Question 5</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">18</td><td style=";">P1</td><td style=";">V2</td><td style=";">Tab 2</td><td style=";">Question 1</td><td style=";">P8</td></tr><tr><td style="color: #161120;text-align: center;">19</td><td style=";">P1</td><td style=";">V2</td><td style=";">Tab 2</td><td style=";">Question 2</td><td style=";">P41</td></tr><tr><td style="color: #161120;text-align: center;">20</td><td style=";">P1</td><td style=";">V2</td><td style=";">Tab 2</td><td style=";">Question 3</td><td style=";">P5</td></tr><tr><td style="color: #161120;text-align: center;">21</td><td style=";">P1</td><td style=";">V2</td><td style=";">Tab 3</td><td style=";">Question 1</td><td style="text-align: right;;">2</td></tr><tr><td style="color: #161120;text-align: center;">22</td><td style=";">P1</td><td style=";">V2</td><td style=";">Tab 3</td><td style=";">Question 2</td><td style="text-align: right;;">4</td></tr><tr><td style="color: #161120;text-align: center;">23</td><td style=";">P1</td><td style=";">V2</td><td style=";">Tab 3</td><td style=";">Question 3</td><td style="text-align: right;;">1</td></tr><tr><td style="color: #161120;text-align: center;">24</td><td style=";">P1</td><td style=";">V3</td><td style=";">Tab 1</td><td style=";">Question 1</td><td style=";">
</td></tr><tr><td style="color: #161120;text-align: center;">25</td><td style=";">P1</td><td style=";">V3</td><td style=";">Tab 1</td><td style=";">Question 2</td><td style="text-align: right;;">3</td></tr><tr><td style="color: #161120;text-align: center;">26</td><td style=";">P1</td><td style=";">V3</td><td style=";">Tab 1</td><td style=";">Question 3</td><td style="text-align: right;;">5</td></tr><tr><td style="color: #161120;text-align: center;">27</td><td style=";">P1</td><td style=";">V3</td><td style=";">Tab 1</td><td style=";">Question 4</td><td style="text-align: right;;">6</td></tr><tr><td style="color: #161120;text-align: center;">28</td><td style=";">P1</td><td style=";">V3</td><td style=";">Tab 1</td><td style=";">Question 5</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">29</td><td style=";">P1</td><td style=";">V3</td><td style=";">Tab 2</td><td style=";">Question 1</td><td style=";">P12</td></tr><tr><td style="color: #161120;text-align: center;">30</td><td style=";">P1</td><td style=";">V3</td><td style=";">Tab 2</td><td style=";">Question 2</td><td style=";">P63</td></tr><tr><td style="color: #161120;text-align: center;">31</td><td style=";">P1</td><td style=";">V3</td><td style=";">Tab 2</td><td style=";">Question 3</td><td style=";">P4</td></tr><tr><td style="color: #161120;text-align: center;">32</td><td style=";">P1</td><td style=";">V3</td><td style=";">Tab 3</td><td style=";">Question 1</td><td style="text-align: right;;">2</td></tr><tr><td style="color: #161120;text-align: center;">33</td><td style=";">P1</td><td style=";">V3</td><td style=";">Tab 3</td><td style=";">Question 2</td><td style=";">
</td></tr><tr><td style="color: #161120;text-align: center;">34</td><td style=";">P1</td><td style=";">V3</td><td style=";">Tab 3</td><td style=";">Question 3</td><td style="text-align: right;;">21</td></tr><tr><td style="color: #161120;text-align: center;">35</td><td style=";">P3</td><td style=";">V1</td><td style=";">Tab 1</td><td style=";">Question 1</td><td style="text-align: right;;">1</td></tr><tr><td style="color: #161120;text-align: center;">36</td><td style=";">P3</td><td style=";">V1</td><td style=";">Tab 1</td><td style=";">Question 2</td><td style=";">P3</td></tr><tr><td style="color: #161120;text-align: center;">37</td><td style=";">P3</td><td style=";">V1</td><td style=";">Tab 1</td><td style=";">Question 3</td><td style="text-align: right;;">8</td></tr><tr><td style="color: #161120;text-align: center;">38</td><td style=";">P3</td><td style=";">V1</td><td style=";">Tab 2</td><td style=";">Question 1</td><td style="text-align: right;;">4</td></tr><tr><td style="color: #161120;text-align: center;">39</td><td style=";">P3</td><td style=";">V2</td><td style=";">Tab 1</td><td style=";">Question 1</td><td style="text-align: right;;">1</td></tr><tr><td style="color: #161120;text-align: center;">40</td><td style=";">P3</td><td style=";">V2</td><td style=";">Tab 1</td><td style=";">Question 2</td><td style=";">P6</td></tr><tr><td style="color: #161120;text-align: center;">41</td><td style=";">P3</td><td style=";">V2</td><td style=";">Tab 1</td><td style=";">Question 3</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">42</td><td style=";">P3</td><td style=";">V2</td><td style=";">Tab 2</td><td style=";">Question 1</td><td style="text-align: right;;">6</td></tr><tr><td style="color: #161120;text-align: center;">43</td><td style=";">P3</td><td style=";">V3</td><td style=";">Tab 1</td><td style=";">Question 1</td><td style="text-align: right;;">2</td></tr><tr><td style="color: #161120;text-align: center;">44</td><td style=";">P3</td><td style=";">V3</td><td style=";">Tab 1</td><td style=";">Question 2</td><td style=";">P3</td></tr><tr><td style="color: #161120;text-align: center;">45</td><td style=";">P3</td><td style=";">V3</td><td style=";">Tab 1</td><td style=";">Question 3</td><td style="text-align: right;;">8</td></tr><tr><td style="color: #161120;text-align: center;">46</td><td style=";">P3</td><td style=";">V3</td><td style=";">Tab 2</td><td style=";">Question 1</td><td style="text-align: right;;">2</td></tr></tbody></table>

Data Input​

Worksheet 1 Output
Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</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="font-weight: bold;;">Version</td><td style="font-weight: bold;text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">Tab</td><td style="font-weight: bold;;">Question #</td><td style="font-weight: bold;;">V1</td><td style="font-weight: bold;;">V2</td><td style="font-weight: bold;;">V3</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">Tab 1</td><td style=";">Question 1</td><td style=";">
</td><td style=";">
</td><td style=";">
</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">Tab 1</td><td style=";">Question 2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">
</td><td style="text-align: right;;">3</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style=";">Tab 1</td><td style=";">Question 3</td><td style=";">
</td><td style="text-align: right;;">2</td><td style="text-align: right;;">5</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style=";">Tab 1</td><td style=";">Question 4</td><td style="text-align: right;;">8</td><td style="text-align: right;;">6</td><td style="text-align: right;;">6</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style=";">Tab 1</td><td style=";">Question 5</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;">8</td><td style=";">Tab 2</td><td style=";">Question 1</td><td style=";">P23</td><td style=";">P8</td><td style=";">P12</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style=";">Tab 2</td><td style=";">Question 2</td><td style=";">P73</td><td style=";">P41</td><td style=";">P63</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style=";">Tab 2</td><td style=";">Question 3</td><td style=";">P5</td><td style=";">P5</td><td style=";">P4</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style=";">Tab 3</td><td style=";">Question 1</td><td style=";">
</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style=";">Tab 3</td><td style=";">Question 2</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style=";">
</td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style=";">Tab 3</td><td style=";">Question 3</td><td style=";">
</td><td style="text-align: right;;">1</td><td style="text-align: right;;">21</td></tr></tbody></table>

P1​

Worksheet #2 Output
<table border="0" cellpadding="0" cellspacing="0" width="333"><colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> <col style="width:48pt" span="3" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:48pt" height="17" width="64">Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</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="font-weight: bold;;">Version</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="font-weight: bold;;">Tab</td><td style="font-weight: bold;;">Question #</td><td style="font-weight: bold;;">V1</td><td style="font-weight: bold;;">V2</td><td style="font-weight: bold;;">V3</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">Tab 1</td><td style=";">Question 1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">Tab 1</td><td style=";">Question 2</td><td style=";">P3</td><td style=";">P6</td><td style=";">P3</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style=";">Tab 1</td><td style=";">Question 3</td><td style="text-align: right;;">8</td><td style="text-align: right;;">
</td><td style="text-align: right;;">8</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style=";">Tab 2</td><td style=";">Question 1</td><td style="text-align: right;;">4</td><td style="text-align: right;;">6</td><td style="text-align: right;;">2</td></tr></tbody></table>

P3​

Does anyone have any ideas? Any input is greatly appreciated!
</td> <td style="width:58pt" width="77">
</td> <td class="xl63" style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl63" style="height:12.75pt" height="17">
</td> <td class="xl63">
</td> <td class="xl64">
</td> <td class="xl64">
</td> <td class="xl64">
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td>
</td> <td align="right">
</td> <td align="right">
</td> <td align="right">
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td>
</td> <td align="right">
</td> <td>
</td> <td align="right">
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td>
</td> <td align="right">
</td> <td align="right">
</td> <td align="right">
</td> </tr> </tbody></table>