Auto-refresh pivot tables in worksheet_change event?

Yay, thanks

I may have spoken too soon.
The pivot tables need not be, and indeed generally will not be, in the same worksheet as the source data.
Currently this code as suggested appears to have no noticeable effect. It certainly is not refreshing the pivot tables in other worksheets.

Still no joy.

Hopefully the attached illustrates the problem.

The intended effect is:
Change the numerical values in either of Source!C3 or Source!C4 and the value in Pivot!B4 should recalculate without the requirement refresh manually.

If it makes a difference: Excel 2003.

Thanks

Still no joy. I have tried both revised solutions (removing the If statement from the worksheet_change code, and including a worksheet activate code in the pivot sheet). But it still does not refresh except manually. Revised workbook incorporating those suggestions is attached to this reply. Tom1977, what causes you to conclude that there is something wrong with the file itself?

Incidentally, if I can get a solution to work that ONLY involves putting code into the source sheet (or general module or under the workbook itself) then that would be ideal, as I may want to grant the end user permission to add further pivot tables in additional sheets, and I would not want to put them to the trouble of having to add VBA code to the new sheets. If I have to live without that ideal then so be it. So far, I have not been able to get either approach to work.

Addendum. This may be of interest:

If I open the file from disk, I get no error messages. It doesn't refresh the pivot table, but neither does it present an error message. The same applies if I download the file from the web page and save it to disk before then opening it from that location.

But if I open it directly by clicking on the link in this web page, then when I activate the pivot sheet I get

Run-time error 1004: Cannot open pivottable source file '[PivotAutochange[1].xls]Source'.
Then the debug line highlighted is the line

Please Login or Register  to view this content.

in the Worksheet_Activate code behind the pivot sheet.

Humm.
It now seems to be working and auto-refreshing. I just closed Excel and re-opened it and the Worksheet_Activate code did the trick. Not an ideal solution because as I say the code resides in the pivot table sheet rather than (preferred) in the source data sheet. I shall leave the thread marked unsolved for the time being in case anyone has a suggestion to get over that hurdle.

Anyway, the current (half)working model is now attached hereto.