How to auto-refresh Pivot data when open Excel file?

[Julie]

Good day everybody,

I would like to know how do I make the Pivot table in my Excel file
auto-refresh data when the file is open? This will ensure that my users see
the latest data without having to manually run the refresh data option.

Thanks in advance and have a nice day.

Best regards,
Julie

 

[Myrna Larson]

You will have to write a macro that runs, say, when the workbook is opened,
that refreshes the table(s). The code would go in the Workbook_Open event
macro.

 

[Julie]

Thanks, Myrna.

I want to try your method. I believe the code is ActiveWorkbook.RefreshAll
but how do I put it in Workbook_Open event. I couldn't find how in the Help.
Instead I found that I can refresh via this method.
1. On the PivotTable toolbar, click PivotTable or PivotChart, and then click
Table Options or Options.
2. Select the Refresh on open check box.

But then when the workbook is open, user has the option to select Enable
refresh or Disable refresh. I don't want that option and want it automatic
refresh.

Can you give me steps how to put in Workbook_Open event?

Thanks again.

Best regards,
Julie

 

[Myrna Larson]

If that's the correct command, in the VB Editor, select your workbook over in
the Project pane, then expand the Microsoft Excel Objects section, then double
click on ThisWorkbook. Above the code pane on the right, you see a drop-down
that says (General). Click the arrow and select Workbook. That will
automatically create the skeleton for a Workbook_Open event macro. Put your
code between the Sub and End Sub lines.