Can Pivot Tables refresh automatically?

[LoriD]

I want to build some Pivot Tables on an existing Pivot Table. Does that mean that whenever some data changes, I have to go in and update all Pivot Tables?

 

[DonkeyOte]

if the pivot tables don't move you can refresh them automatically...

Sheets("SHEET1").Select
Set PvtTable = Worksheets("SHEET1").Range("A1").PivotTable
PvtTable.RefreshTable

etc...

 

[LoriD]

I'm not sure what you mean by, "If the Pivot Tables don't move".
The rows will expand as records are added, but the headings will remain.

And where do I put this code? In the worksheet module?

Thanks for your time with this... Lori

 

[DonkeyOte]

I mean the first cell - ie. where you position your pivot table - normally A3 - the table may expand but the root position doesn't i.e. a3

The problems will arise when you have multiple Pivots on one sheet, this means when the one in top left expands it will shift the others' root position.

You could name the PO area to be the first value in the top left of the results - this may solve the problem.

 

[LoriD]

Thanks for your quick reply.
OK, I'm set on how the table changes. But where do I place this code? I tried pasting it into a module in the Pivot Table worksheet, and then adding some data, but it didn't change, so I must be doing something wrong...
Lori

 

[DonkeyOte]

So you ran the macro and it didn't refresh?

Strange.

If you send it to me I would be happy to check it out. If not, post the code your running.

 

[LoriD]

I know what I did. (Or didn't do.) I need an event to trigger the macro, right? Short of manually running the macro with a button or something, is there a way I can make it run once a user finishes entering (or deleting) a record? A record occupies columns A:L.