Variable Pivot Chart Fields in Macro

[mileskb]

I have a macro that prints sets of Pivot charts, based on a number of Fields. For example:

ActiveChart.PivotLayout.PivotTable.PivotFields("Department").CurrentPage = "GI"

this sets the Pivot Field called "Department" to the value "GI" and then the macro prints the chart. I'd like to use variable data for the Date Pivot Field, so that I can set Pivot Field called "Date" to be "Today(), Today()-1, Today()-2...."

Is there a way to do this in the macro?

Thanks,
-Miles

 

[RoryA]

I was running that code in Excel 2003 SP2, using a date field in the row area of the table, so I'm a little puzzled! Do you actually have data for today's date?

 

[mileskb]

Yes, I have data for today and each day of the past month. I've tried a couple values in there and no luck. Also, I can't group the field even though it is a date field. So something tells me it doesn't recognize the data as dates.

I have a hard time with the Group By function of Pivot Tables - it seems to be hit or miss a lot of time.

 

[mileskb]

Rory, I just created a simple example in another workbook and the format(Now, "m/d/yyyy") worked just fine in the row field. It must be my data that doesn't want to cooperate. I just recognized another complication too - if I use Now - 1, and there is no data for yesterday, then the code errors out. So this wouldn't be foolproof anyways. I'm just going to have the users go the manual route to do this and fix the sheet when it's inevitably broken down the road

Thanks again for your help on all of this.

-Miles

 

[RoryA]

If your date data in the PT has, or had, blanks in it, then it would be treated as text. You should also check fir spaces in the data especially if it's from an external source. You can program round the missing dates too.

 

[mileskb]

There are blanks at the end of the pivot selection, so that's what was causing the date issue for group by. However, it did not solve the macro being unable to read "3/31/2009". I multiplied all cells by 1 and reformatted to be date m/d/yyyy, so everything should be a date. Still no luck.

 

[RoryA]

You would have to rebuild the PT usually for it to treat the dates as dates once it has treated them as text.

 

[mileskb]

Yeah, I did rebuild the PT to include only non-blanks and still got the same result. Assuming I can figure out the date issue, how could I get around the missing date errors?

 

[RoryA]

Probably the easiest way is to use a countif on the original data - if it's 0 then don't try and filter.

 

[mileskb]

Thanks