Using VBA to change a pivot table filter, refresh the report and set up a refresh delay

[Bonnister]

Hello

I'm going round in circles trying to figure out the following:

I have a pivot table that displays cumulative percentages over time (the time period in question being months), with a report filter at the top allowing the user to pick a month. Using some clever macro I want to be able to do the following:

1) Running the macro will change the month in the filter sequentially (Jan 12, Feb 12, Mar 12 etc)
2) After each change is made to the filter the report refreshes itself
3) Each occurence last for a few seconds (in this instance let's say 5)

In other words, as soon as I select the macro it will change the filter, refresh the report, keep the results up on screen for a few seconds and then change the filter to the next month in sequence, refresh and display etc etc

Is this possible? Any help is gratefully received

Many thanks
Kenny

[JosephP]

is the report filter part of the pivot table itself (in which case the refresh is automatic) or something you have built?

[Bonnister]

Hi

Yes it is, at the moment it's just a standard pivot table that I've set up

Thanks
Kenny

[JosephP]

in that case this oughta do it (all in a normal module)

Please Login or Register  to view this content.

[Bonnister]

Hello again

Thanks for that code, I've entered it and firstly ran the CycleFilters macro. After the month correctly changed to the next in sequence, the following message appeared:

"Cannot run the macro "...nextItem'. The macro may not be available in this workbook or all macros may be disabled.

I also get the same error message if I try running the NextItem macro. The workbook is saved as macro-enabled, and I've entered the code into a standard module within the active workbook. Do you know what this error means and is there anything you can offer as help please (I'm very much a VBA novice so your help is appreciated)

Many thanks
Kenny

[romperstomper]

Are you sure it is in a standard module - eg Module1 and not a worksheet or ThisWorkbook module? Does it appear in the macros dialog list?

[JosephP]

is it definitely in a normal module?

[Bonnister]

Sorry! My bad, I'd entered it into the worksheet, just moved it into a module and it now works fine, thanks

Just for my own understanding, why does putting the code into a worksheet instead of a module cause the problem I had before?

[JosephP]

if you put it in a worksheet module, it becomes a method of that sheet and you have to call it accordingly - sheet1.nextitem instead of just nextitem for instance.