Need to create a macro using pivot table values and totals (not headers)

[clhmms]

Greetings,

I need help. I have searched the web and have thus far come up with nothing. This is what I need to do.

I need to create a macro using pivot table values and totals. The macro will need to determine the cell contents and based on the type of cell contents either perform a calculation and paste the data into a new cell or paste the data into the cell as is.

If the cell contains anything other then a pivot table value or pivot table total then, the macro will paste the contents in an area next to the pivot table. If the cell contains a pivot table value or pivot table total then, the macro will perform a calculation and paste result into an area next to the pivot table.

Example 1: cell F26 contains “# Sold” which is not a pivot table value, the macro will paste “# Sold” in cell L26.
Example 2: cell F28 contains “Pen A” which is not a pivot table value, the macro will paste “Pen A” in cell L28.
Example 3: cell H28 contains “14” which is a pivot table value, the macro will multiply H28 * 2 and paste the result of “28” in cell N28.
Example 4: cell J28 contains “20” which is a pivot table total, the macro will multiply J28 * 2 and paste the result of “40” in cell P28.

The final result will look like the table under the header “#1) Results of Macro”

Does anyone have any idea about how to create a macro that will read all of the cells in a pivot table and product the results I need?

--------------------------------------------------------
I do not know how to attach an xls doc with my example so I pasted a table below. It does not display as clearly as my xls doc.


<table width="795" border="0" cellpadding="0" cellspacing="0"><col style="width: 65pt;" width="86"> <col style="width: 40pt;" width="53"> <col style="width: 41pt;" width="55"> <col style="width: 41pt;" width="55"> <col style="width: 62pt;" width="82" span="2"> <col style="width: 48pt;" width="64" span="3"> <col style="width: 56pt;" width="75"> <col style="width: 86pt;" width="115"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 65pt;" width="86" height="17">Pivot Table</td> <td class="xl25" style="width: 40pt;" width="53">
</td> <td class="xl25" style="width: 41pt;" width="55">
</td> <td class="xl26" style="width: 41pt;" width="55">
</td> <td class="xl26" style="width: 62pt;" width="82">
</td> <td class="xl26" style="width: 62pt;" width="82">
</td> <td class="xl24" colspan="2" style="width: 96pt;" width="128">#1) Results of Macro </td> <td class="xl25" style="width: 48pt;" width="64">
</td> <td class="xl25" style="width: 56pt;" width="75">
</td> <td class="xl25" style="width: 86pt;" width="115">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17"># Sold</td> <td class="xl27">
</td> <td class="xl27">Yr-Mo</td> <td class="xl27">
</td> <td class="xl27">
</td> <td class="xl27">
</td> <td class="xl25"># Sold</td> <td class="xl25">
</td> <td class="xl25">Yr-Mo</td> <td class="xl25">
</td> <td class="xl25">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl28" style="height: 12.75pt;" height="17">Prod</td> <td class="xl28">Color</td> <td class="xl29">201010</td> <td class="xl30">201011</td> <td class="xl31">Grand Total</td> <td class="xl32">
</td> <td class="xl25">Prod</td> <td class="xl25">Color</td> <td class="xl25" align="right">201010</td> <td class="xl25" align="right">201011</td> <td class="xl25">Grand Total</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl33" style="height: 13.5pt;" height="18">Pen A</td> <td class="xl34">Blue</td> <td class="xl35">14</td> <td class="xl34">6</td> <td class="xl36">20</td> <td class="xl37">
</td> <td class="xl25">Pen A</td> <td class="xl25">Blue</td> <td class="xl38" align="right">28</td> <td class="xl39" align="right">12</td> <td class="xl40" align="right">40</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl41" style="height: 13.5pt;" height="18"> </td> <td class="xl37">Red</td> <td class="xl42">65</td> <td class="xl37">8</td> <td class="xl43">73</td> <td class="xl37">
</td> <td class="xl25">
</td> <td class="xl25">Red</td> <td class="xl44" align="right">130</td> <td class="xl25" align="right">16</td> <td class="xl45" align="right">146</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl46" style="height: 13.5pt;" height="18">Pen A Total</td> <td class="xl47"> </td> <td class="xl48">79</td> <td class="xl48">14</td> <td class="xl49">93</td> <td class="xl50">
</td> <td class="xl25" colspan="2" style="">Pen A Total</td> <td class="xl44" align="right">158</td> <td class="xl25" align="right">28</td> <td class="xl45" align="right">186</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl51" style="height: 13.5pt;" height="18"> </td> <td class="xl52"> </td> <td class="xl35"> </td> <td class="xl34"> </td> <td class="xl36"> </td> <td class="xl37">
</td> <td class="xl25">
</td> <td class="xl25">
</td> <td class="xl44"> </td> <td class="xl25">
</td> <td class="xl45"> </td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl33" style="height: 13.5pt;" height="18">Pen B</td> <td class="xl34">Blue</td> <td class="xl35">25</td> <td class="xl34">72</td> <td class="xl36">97</td> <td class="xl37">
</td> <td class="xl25">Pen B</td> <td class="xl25">Blue</td> <td class="xl44" align="right">50</td> <td class="xl25" align="right">144</td> <td class="xl45" align="right">194</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl41" style="height: 13.5pt;" height="18"> </td> <td class="xl37">Red</td> <td class="xl42">3</td> <td class="xl37">11</td> <td class="xl43">14</td> <td class="xl37">
</td> <td class="xl25">
</td> <td class="xl25">Red</td> <td class="xl44" align="right">6</td> <td class="xl25" align="right">22</td> <td class="xl45" align="right">28</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl46" style="height: 13.5pt;" height="18">Pen B Total</td> <td class="xl47"> </td> <td class="xl48">28</td> <td class="xl48">83</td> <td class="xl49">111</td> <td class="xl50">
</td> <td class="xl25" colspan="2" style="">Pen B Total</td> <td class="xl44" align="right">56</td> <td class="xl25" align="right">166</td> <td class="xl45" align="right">222</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl51" style="height: 13.5pt;" height="18"> </td> <td class="xl52"> </td> <td class="xl35"> </td> <td class="xl34"> </td> <td class="xl36"> </td> <td class="xl37">
</td> <td class="xl25">
</td> <td class="xl25">
</td> <td class="xl44"> </td> <td class="xl25">
</td> <td class="xl45"> </td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl33" style="height: 13.5pt;" height="18">Pen C</td> <td class="xl34">Blue</td> <td class="xl35">3</td> <td class="xl34">68</td> <td class="xl36">71</td> <td class="xl37">
</td> <td class="xl25">Pen C</td> <td class="xl25">Blue</td> <td class="xl44" align="right">6</td> <td class="xl25" align="right">136</td> <td class="xl45" align="right">142</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl41" style="height: 13.5pt;" height="18"> </td> <td class="xl37">Red</td> <td class="xl42">98</td> <td class="xl37">82</td> <td class="xl43">180</td> <td class="xl37">
</td> <td class="xl25">
</td> <td class="xl25">Red</td> <td class="xl44" align="right">196</td> <td class="xl25" align="right">164</td> <td class="xl45" align="right">360</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl46" style="height: 13.5pt;" height="18">Pen C Total</td> <td class="xl47"> </td> <td class="xl48">101</td> <td class="xl48">150</td> <td class="xl49">251</td> <td class="xl50">
</td> <td class="xl25" colspan="2" style="">Pen C Total</td> <td class="xl44" align="right">202</td> <td class="xl25" align="right">300</td> <td class="xl45" align="right">502</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl51" style="height: 13.5pt;" height="18"> </td> <td class="xl52"> </td> <td class="xl35"> </td> <td class="xl34"> </td> <td class="xl36"> </td> <td class="xl37">
</td> <td class="xl25">
</td> <td class="xl25">
</td> <td class="xl44"> </td> <td class="xl25">
</td> <td class="xl45"> </td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl33" style="height: 13.5pt;" height="18">Pen D</td> <td class="xl34">Blue</td> <td class="xl35">66</td> <td class="xl34">91</td> <td class="xl36">157</td> <td class="xl37">
</td> <td class="xl25">Pen D</td> <td class="xl25">Blue</td> <td class="xl44" align="right">132</td> <td class="xl25" align="right">182</td> <td class="xl45" align="right">314</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl41" style="height: 13.5pt;" height="18"> </td> <td class="xl37">Red</td> <td class="xl42">31</td> <td class="xl37">43</td> <td class="xl43">74</td> <td class="xl37">
</td> <td class="xl25">
</td> <td class="xl25">Red</td> <td class="xl44" align="right">62</td> <td class="xl25" align="right">86</td> <td class="xl45" align="right">148</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl46" style="height: 13.5pt;" height="18">Pen D Total</td> <td class="xl47"> </td> <td class="xl48">97</td> <td class="xl48">134</td> <td class="xl49">231</td> <td class="xl50">
</td> <td class="xl25" colspan="2" style="">Pen D Total</td> <td class="xl44" align="right">194</td> <td class="xl25" align="right">268</td> <td class="xl45" align="right">462</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl51" style="height: 13.5pt;" height="18"> </td> <td class="xl52"> </td> <td class="xl35"> </td> <td class="xl34"> </td> <td class="xl36"> </td> <td class="xl37">
</td> <td class="xl25">
</td> <td class="xl25">
</td> <td class="xl44"> </td> <td class="xl25">
</td> <td class="xl45"> </td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl33" style="height: 13.5pt;" height="18">Pen E</td> <td class="xl34">Blue</td> <td class="xl35">41</td> <td class="xl34">22</td> <td class="xl36">63</td> <td class="xl37">
</td> <td class="xl25">Pen E</td> <td class="xl25">Blue</td> <td class="xl44" align="right">82</td> <td class="xl25" align="right">44</td> <td class="xl45" align="right">126</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl41" style="height: 13.5pt;" height="18"> </td> <td class="xl37">Red</td> <td class="xl42">8</td> <td class="xl37">29</td> <td class="xl43">37</td> <td class="xl37">
</td> <td class="xl25">
</td> <td class="xl25">Red</td> <td class="xl44" align="right">16</td> <td class="xl25" align="right">58</td> <td class="xl45" align="right">74</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl46" style="height: 13.5pt;" height="18">Pen E Total</td> <td class="xl47"> </td> <td class="xl48">49</td> <td class="xl48">51</td> <td class="xl49">100</td> <td class="xl50">
</td> <td class="xl25" colspan="2" style="">Pen E Total</td> <td class="xl44" align="right">98</td> <td class="xl25" align="right">102</td> <td class="xl45" align="right">200</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl51" style="height: 13.5pt;" height="18"> </td> <td class="xl52"> </td> <td class="xl35"> </td> <td class="xl34"> </td> <td class="xl36"> </td> <td class="xl37">
</td> <td class="xl25">
</td> <td class="xl25">
</td> <td class="xl44"> </td> <td class="xl25">
</td> <td class="xl45"> </td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl53" style="height: 13.5pt;" height="18">Grand Total</td> <td class="xl54"> </td> <td class="xl55">354</td> <td class="xl55">432</td> <td class="xl56">786</td> <td class="xl57">
</td> <td class="xl25" colspan="2" style="">Grand Total</td> <td class="xl58" align="right">708</td> <td class="xl59" align="right">864</td> <td class="xl60" align="right">1572</td> </tr> </tbody></table>
--------------------------------------------
Thank you in advance for your time!
PS I am using Windows XP, Excel 2003

 

[Jerry Sullivan]

Welcome to the Board!

You don't need to step through each cell of the of the PivotTable to evaluate it's type. Instead you can use PivotTable Object members like .TableRange2 and .DataBodyRange to grab and handle each range separately.

Sub Pivot_Process()
    With ActiveSheet.PivotTables("PivotTable1")
        .TableRange2.Copy
        .TableRange2.Offset(0, 6).PasteSpecial (xlPasteValues)
        With .DataBodyRange.Offset(0, 6)
                .FormulaR1C1 = "=RC[-6]*2"
                .Value = .Value
        End With
    End With
End Sub

 

[clhmms]

Thank you so much for answering my question!! I spent hours trying to figure this out on my own. I will pass along the helpful energy and do something nice for someone today.

 

[clhmms]

Hello,

Thank you again for your code! I added one line to paste formats. (That took a while!)

A sticky point I have, which I didn't mention in my original question, is that the pivot table can have varying widths. It can be 6 to 20 columns wide. Is there a way to have the results post in the column that is two over from the pivot table. Example:

pivot table data | blank column | Macro results

I have been searching and the answers are above my head.

Thank you in advance for your consideration.
Cassandra

 

[Jerry Sullivan]

Hi Cassandra, I'm happy to hear this worked for you.

Here is some revised Code.
You can modify lngGap to offset the copy with a defined number of columns in between the copy and the original PivotTable.

Sub Pivot_Process2()
    Dim lngOffset As Long
    Dim lngGap As Long: lngGap = 1
    
    With ActiveSheet.PivotTables("PivotTable1")
        With .TableRange2
            lngOffset = .Columns.Count + lngGap
            .Copy
            .Offset(0, lngOffset).PasteSpecial _
                (xlPasteValues)
            .Offset(0, lngOffset).PasteSpecial _
                (xlPasteFormats)
        End With
        With .DataBodyRange.Offset(0, lngOffset)
                .FormulaR1C1 = "=RC[-" & lngOffset & "]*2"
                .Value = .Value
        End With
    End With
End Sub

 

[clhmms]

Well that looks intimidating...lol. I will try it out and let you know. Thank you again for your time. Now I will do another nice thing for another person today. Have a great day!