Can Pivot Tables refresh automatically?

LoriD

Board Regular
Joined
Apr 1, 2002
Messages
141
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?
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

Board Regular
Joined
Apr 1, 2002
Messages
141
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

Board Regular
Joined
Apr 1, 2002
Messages
141

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

Board Regular
Joined
Apr 1, 2002
Messages
141
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,269
Messages
5,623,727
Members
415,986
Latest member
C_Braga

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top