I just ran into a pivot table VBA issue I've never noticed before. When a pivot table has more than one data field then referring to a PivotItem.DataRange returns a range that is shifted one row down from where it should be. Below you can see the address is off one row and that the selection is below the pivot table - again off by one row:
If the pivot table has only one data field, e.g., if I get rid of "Sum of Total" above, the issue goes away.
I found one reference to this by Macro Marc here on SO, but nothing else on the web. Does anybody have any knowledge of this, e.g., how consistent it is, the best way to code around it, etc.? I can think of a few ways to check for this, but yuck!
Possible Solution:
In my very limited testing it seems like there isn't a similar issue for PivotFields. So my best idea is to compare the first row of a pivot field against the first row of its first pivot item. However, I'm not sure that my concept of "first" will always be the same as Excel's. Anyways I'm thinking of something like this:
BugCorrection = PivotTable.VisibleFields(1).DataRange.Row - _
PivotTable.VisibleFields(1).VisibleItems(1).DataRange.Row