Pivot Table VBA code not working properly

[boldcode]

Hi,

I need a little help with my code. I have a pivot table report that I created and I added some code to it change the background color of the PivotFields. The beginning of the code works, but the last bit of code does not work. I have provided samples below of the code that works and the one that doesn't.


This piece of code works fine:
Range("D9").Select
ActiveSheet.PivotTables("rptInterest").PivotFields("Location")
.Caption = "SUPSHIP"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 35
Selection.Interior.ColorIndex = 56

This piece of code DOES NOT work fine:
Range("E9:" & LastColumn).Select
ActiveSheet.PivotTables("rptInterest").PivotFields ("Year")
Selection.Font.Bold = True
Selection.Font.ColorIndex = 35
Selection.Interior.ColorIndex = 56

What I want the last piece of code to is change the font and background cell color to the ones I have chosen for the pivot field "Year". In cell E9 the field title is 2010, E10: 2011, E11:2012, etc...I want these changes to take hold to the very last year in the pivot table.

 

[Marcelo Branco]

Hi,

Please explain these lines
Range("E9:" & LastColumn).Select
ActiveSheet.PivotTables("rptInterest").PivotFields ("Year")

What is LastColumn?

and what is the purpose of this
ActiveSheet.PivotTables("rptInterest").PivotFields ("Year")
?

M.

 

[boldcode]

Marcelo Branco,

Please explain these lines
Range("E9:" & LastColumn).Select
ActiveSheet.PivotTables("rptInterest").PivotFields ("Year")

Maybe this visual will help:

<table style="width: 1171px; height: 126px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2742; width:56pt" span="5" width="75"> <col style="mso-width-source:userset;mso-width-alt:2706;width:56pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:2779;width:57pt" width="76"> <col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> </colgroup><tbody><tr style="height:13.5pt" height="18"> <td style="height:13.5pt;width:48pt" height="18" width="64">
</td> <td class="xl67" style="width:56pt" width="75">COLUMN A</td> <td class="xl67" style="width:56pt" width="75">COLUMN B</td> <td class="xl67" style="width:56pt" width="75">COLUMN C</td> <td class="xl67" style="width:56pt" width="75">COLUMN D</td> <td class="xl67" style="width:56pt" width="75">COLUMN E</td> <td class="xl67" style="width:56pt" width="74">COLUMN F</td> <td class="xl67" style="width:57pt" width="76">COLUMN G</td> <td class="xl67" style="width:56pt" width="75">COLUMN H</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt" height="17">ROW 9</td> <td class="xl68">REED</td> <td class="xl68">TYPE</td> <td class="xl68">NAME</td> <td class="xl68">BRAND</td> <td class="xl68">2010</td> <td class="xl69">2011</td> <td class="xl69">2012</td> <td class="xl70">2013</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt" height="17">ROW 10</td> <td class="xl71">JK</td> <td class="xl71">KL</td> <td class="xl71">OTHER</td> <td class="xl71">MEGO</td> <td class="xl71">
</td> <td class="xl72">450</td> <td class="xl72">
</td> <td class="xl73">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt" height="17">ROW 11</td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl74">
</td> <td class="xl75">DECK</td> <td class="xl75">
</td> <td class="xl67">890</td> <td class="xl67">900</td> <td class="xl76">
</td> </tr> </tbody></table>

Basically I want the years 2010 to what ever the last one could be to take on the font and background colors of my choosing.



What is LastColumn?

The LastColumn is suppose to represent whatever the last column is on the pivot table. I don't want to constrain it to column H because the Year could go out to 2030

and what is the purpose of this
ActiveSheet.PivotTables("rptInterest").PivotFields ("Year")
?

this is piece of code is suppose to represent the pivot field "Year" in which I get 2010, 2011, 2012, etc...