Link to home
Start Free TrialLog in
Avatar of nitinkrjain
nitinkrjainFlag for India

asked on

Auto refresh a pivot table in excel 2010

Using Excel 2010 it seems the Pivot data  does not refresh automatically . Is there to be another way to do it ?

Basically, in  workbook whenever data is modified  then pivot table should automatically refresh currently we have to click on refresh button, the same is working in previous versions i.e. 2007, 2003.
Avatar of Xaelian
Xaelian
Flag of Belgium image

Hi,

I can give you 2 methods. There are 2 options. 1 is easy, it's change an option in your Excel, to automaticaly refresh your pivot table when you open your workbook. The other one is inserting vba in your workbook.

Using VBA for automatically refreshing data when you're at your pivottable tab.
1. Right click on the sheet name where you placed the pivot table
2. Click "View code"
3. Paste code into code window
4. Change sheet name and pivot table name in vba code
Private Sub Worksheet_Activate()
    Sheets("Pivot table").PivotTables("PivotTable1").RefreshTable
End Sub

Open in new window


Refresh PivotTable data automatically when opening the workbook
1. Click anywhere in the PivotTable report.
This displays the PivotTable Tools, adding an Options and a Design tab.
2. On the Options tab, in the PivotTable group, click Options.
3. In the PivotTable Options dialog box, on the Data tab, select the Refresh data when opening the file check box.
Avatar of Rory Archibald
BTW there is no difference here between 2010 and previous versions so if you had it working somehow in an earlier version, the same method should work in 2010.
Avatar of nitinkrjain

ASKER

@ Xaelian
We have tried the VBA code already but its not working , we need to run this code everytime, it does not work automatically.

The second option you told is the problem it self , we want data to be refreshed without clicking refresh button
I think you misunderstand. You can enable the checkbox: "Refresh data when opening the file". This will refresh the data in your pivottable.

I'll take a look at other opions.
Use following code.

It will refresh your pivot table if you changed colums D or E.
Private Sub Worksheet_Change(ByVal Target As Range)
'
' Update the pivot table on this worksheet if data in columns D or E is changed
'
If (Target.Column = 4) Or (Target.Column = 5) Then
ActiveSheet.PivotTables(1).RefreshTable
End If
End Sub

Open in new window

HI  Xaelian

Can you send us a sample excel file , as it is not working here in excel 2010

Thanks
Hi,

Here you go. You can only change the data in column D and E. Column C is just as a reference so you can see perfectly in the pivot table he changes the values, when you edit something in column D or E in the table.
example.xlsm
if we use another sheet for pivot table then its showing error , if data  & table are on same sheet then  its working.
plz advise
ASKER CERTIFIED SOLUTION
Avatar of Xaelian
Xaelian
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Good & prompt solution, Thanks
Thanks Xaelian

I need one more help in excel, if you can help , please check ..

https://www.experts-exchange.com/questions/27834658/Password-protected-row-wise-data-entry-in-excel-2010.html