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

 

[mileskb]

*Bump*

 

[RoryA]

Much the same way:

PivotFields("Date").CurrentPage = Format(Date - 1, "dd/mm/yyyy")

using the appropriate formatting for your table.

 

[mileskb]

Thanks for the suggestion Rory, but I added the following line:

ActiveChart.PivotLayout.PivotTable.PivotFields("Date").CurrentPage = Format(Now, "m/d/yyyy")

And it errors out on that line.

I'm using the Format(Now, "m-d-yy") function elsewhere to date stamp and it works fine there, so I assume the Now value is working fine.

 

[RoryA]

The format you use has to match the format of the dates in the list.

 

[mileskb]

The format for the Field is "m/d/yyyy"

I'm even more confused now. I just recorded a macro, by manually selecting today's date and then stopped the recording. Here is the macro, as recorded:

With ActiveChart.PivotLayout.PivotTable.PivotFields("Date")
.PivotItems("3/31/2009").Visible = True
End With

I then unchecked today's date and ran the macro, to see if it would check today's date, and it still errored out. It won't even run the exact macro that was recorded. What would cause this?

 

[mileskb]

The field I am trying to modify is at the bottom of the Pivot Chart, if that makes a difference - which I assume is why the recorded code looks different than what I use for the fields at the top of the chart.

 

[RoryA]

If it's a rowfield (or columnfield) then you can't use CurrentPage. When changing those, you have to make sure that at least one item is visible for the pivot field, and it's also safest to set the table's manualupdate property to true, make the change, then set it back to false.
I just tested with this code using m/d/yy format dates in the table:

   Dim cht As Chart
   Dim pt As PivotTable, pi As PivotItem
   Set cht = ActiveChart
   Set pt = cht.PivotLayout.PivotTable
   With pt
      .ManualUpdate = True
      With .PivotFields("Date")
         .PivotItems(Format(Date, "m/d/yy")).visible = True
         For Each pi In .PivotItems
            pi.visible = (pi.Caption = Format(Date, "m/d/yy"))
         Next pi
      End With
      .ManualUpdate = False
   End With

 

[mileskb]

It still gives me an error once it hits .PivotItems(Format(Date, "m/d/yy")).Visible = True

Could this be a version problem? I'm using 2003 SP2, if that makes a difference at all.

I changed the format in the code to m/d/yyyy (since that is how the data is, 2009 and not 09), but to no avail. I tried running both ways /yy and /yyyy, with no luck. I also tried changing the format of the data to m/d/yy but it won't take. The chart and the drop down list only display m/d/yyyy, regardless of what I set the format to for the cells.

I appreciate the input Rory. Hopefully I can come to some solution, but if not, I'll just have to work on the users to select dates manually. The users of this file are point-and-click users; any time I introduce more than 2 steps, things go awry. So I try to do as much as I can in macros. This may be one of those things that I can't solve though. Thanks again.

-Miles

 

[mileskb]

OK, so it's almost definitely the Date value that is causing the problem. I just dragged another Field onto the Chart (MachineName) and wrote a macro to select "46". I ran that macro and it worked correctly. I took the MachineName off, edited the macro to be Date and "3/31/2009" and it errors out. I've tried multiplying all dates by 1 and reformatting back to dates and that doesn't solve it either. Will date selection just not work in a row field?

The Format(Now, "m/d/yyyy") works fine in the header by the way, so that proves out the Date portion of the code is correct. The MachineName test proves out that you can use macros for the row fields. The only thing that won't work is Date in the row fields.....Frustrating to say the least.