For each.......Next Loop 1

[JohnAcc]

Does anyone know how to update all the pivot tables on a sheet with a for next loop?

I can't seem to get the coding right.

Thanx in advance

Rgds, John

 

[xlbo]

for each pt in sheets("Sheetname&quot.pivottables
pt.refreshtable
Next

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[comconrk]

I have tried that code and it works much better than my individual refreshes. However, out of the 5 pivot tables in
my worksheet a couple of them do not refresh properly. After running this code I need to click on the pivot table, then the refresh button and it refreshes. Why can't I get this code to work on all the pivot tables? Obviously,
I am doing something wrong. I just can't figure out what
it is?

Thanks,

 

[xlbo]

Are they all on the same sheet ??

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[comconrk]

Yes, the pivot tables are all on the same sheet

 

[xlbo]

and some of them do refresh but others don't ???
That is very odd being as the code should be looping through all pivottable objects on the specified sheet...

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[comconrk]

I thought it was very odd also. I maximized the worksheet prior to refreshing the pivot tables and it does refresh each one. However, two of them are not a complete refresh, so I need to click on each one and hit the refresh button.
Then it works correctly.

I am confused at this point.

Thanks,

Ron

 

[xlbo]

What do you mean "Not a complete refresh" ??

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[comconrk]

After the code runs, some of the pivot table contains correct data, while the rest of the pivot table contains zeros.

The non-zero data is not incorrect, it is just that the
data which shows zero is wrong.

Once I do the refresh manually, the data is populated
properly.

 

[xlbo]

Have you tried "stepping through" the code, using F8 ??
Please try this and let me know if you get the same issue

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[comconrk]

Geoff,

I feel embarrassed. Over the weekend I got to thinking about my code and checked it on various conditions. There
were some times when I modified the basic data after I
refreshed the pivot table.

When I changed the order of all that, the refresh works
perfectly.

Sorry to have bothered you, but I did pick up some great code to automatically refresh the pivot tables.

Thanks for all your help,
Ron

 

[xlbo]

No probs - glad you got it sorted

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question