write at exceltoexplore@gmail.com : Report Automation|Dashboard in Excel| Provide Excel consulting through macro (VBA) automation |Financial Modeling | Ethical Hacking

Thursday, 29 December 2011

Excel VBA - Pivot Table Auto referesh

Assuming Your sheet name source data
1. Press ctrl+F3. Name Manager window will appear
2. Click on New
3. Define Name
4. in Refers to text box Enter below formula
=OFFSET('Source Data'!$A$1,0,0,COUNTA('Source Data'!$A:$A),6)
5. Click on ok
6.Now Press Alt + N + V + T short cut keys (or go to insert tab & select Pivot table)
7.Create Pivot table window will appear
8. in Table/Range box enter the name you define in 3 step & click on ok
9. Create Pivot table
10. Right click on pivot table sheet tab
11. click on View code
12. copy & paste below code in vb window ; change pivottable name

Private Sub Worksheet_Activate()
     'If this worksheet is activated, refresh the pivot table
     'Change "Pivot" to your sheet's name
     'Change "PivotTable1" to your pivot table's name

End Sub

13. Now whenever you change base data & select pivot table sheet it will get automatically refresh

No comments:

Post a Comment