?
Solved

Auto refresh a pivot table in excel 2010

Posted on 2012-08-19
11
Medium Priority
?
880 Views
Last Modified: 2012-08-20
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.
0
Comment
Question by:nitinkrjain
  • 5
  • 5
11 Comments
 
LVL 13

Expert Comment

by:Xaelian
ID: 38310767
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.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 38310831
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.
0
 
LVL 1

Author Comment

by:nitinkrjain
ID: 38310877
@ 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
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 13

Expert Comment

by:Xaelian
ID: 38310893
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.
0
 
LVL 13

Expert Comment

by:Xaelian
ID: 38310916
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

0
 
LVL 1

Author Comment

by:nitinkrjain
ID: 38310944
HI  Xaelian

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

Thanks
0
 
LVL 13

Expert Comment

by:Xaelian
ID: 38311033
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
0
 
LVL 1

Author Comment

by:nitinkrjain
ID: 38311051
if we use another sheet for pivot table then its showing error , if data  & table are on same sheet then  its working.
plz advise
0
 
LVL 13

Accepted Solution

by:
Xaelian earned 2000 total points
ID: 38311087
Hi,

Here you have the following example. Code is made in sheet1.
example2.xlsm
0
 
LVL 1

Author Closing Comment

by:nitinkrjain
ID: 38311092
Good & prompt solution, Thanks
0
 
LVL 1

Author Comment

by:nitinkrjain
ID: 38311109
Thanks Xaelian

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

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27834658.html
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question