nitinkrjain
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.
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.
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.
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
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.
I'll take a look at other opions.
Use following code.
It will refresh your pivot table if you changed colums D or E.
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
ASKER
HI Xaelian
Can you send us a sample excel file , as it is not working here in excel 2010
Thanks
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
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
ASKER
if we use another sheet for pivot table then its showing error , if data & table are on same sheet then its working.
plz advise
plz advise
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Good & prompt solution, Thanks
ASKER
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
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
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
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.