opkbc.blogg.se

I have fields formatted as date/time information across the top of my pivottabel, and I amend your code accordingly, I get an error “Unable to get the PivotItems property of the PivotField class”. In the case that you have the Pivottable’s Years field in the columns area, how would you select say the readins for Feb only for the years 2003 – 2005?ģ. In 2007 I need to take the space out to get your code to work.Ģ. PivotTable is one word with no space between Pivot and Table)? Where you have “Dim pt As Pivot Table” above, should this be “Dim pt As PivotTable” (i.e. I’ve a couple of questions for you re this post.ġ. Intersect(pt.PivotFields("Years").Pivotitems("2004").DataRange.EntireRow, pt.PivotFields("Order Date").DataRange).SelectĪ follow-up post shows how to use this new skill to Create and Update a Chart Using Only Part of a Pivot Table’s Data. If a particular range isn’t specifically defined with a VBA property, you can use VBA range-extending properties EntireColumn and EntireRow and range operators Union and Intersect to define the range. The next few examples show the same ranges as above, after pivoting the table’s Years field from the columns area to the rows area. Pt.PivotFields("Order Date").PivotItems("Feb").DataRange.Select Pt.PivotFields("Years").PivotItems("2004").DataRange.Select Pt.PivotFields("Years").PivotItems("2004").LabelRange.Select Pt.PivotFields("Years").LabelRange.Select I’ve highlighted the various ranges using the indicated VBA commands.

In VBA, you can reference a pivot table using this code in a procedure: I’ll illustrate these special ranges using this simple pivot table, which comes from an example formerly available on the Microsoft web site (I can no longer locate it). One important part of this is referencing the various ranges within a pivot table by their special VBA range names (which are actually properties of the Pivot Table object). I thought it would be helpful to show some of the mechanics of programming with pivot tables. These examples included specific procedures, and the emphasis was on the results of the manipulation. I’ve posted several examples of manipulating pivot tables with VBA, for example, Dynamic Chart using Pivot Table and VBA and Update Regular Chart when Pivot Table Updates.