Automatically refresh pivot table on MS excel

[Genna]

I am having the computer with processor core i7 and the operating system in my computer is windows 7. And I have the problem I want to make the pivot Tables but there is a minor difficulty that means I want to make the pivot Tables but it will automatically refreshes when some date has been changed. I have done it with the help of the macro the syntax is as follows:

Private Sub Worksheet Activate ()
ActiveWorkbook.RefreshAll
End Sub

Si this is the syntax that I have created in the macro but hoe to modify the macro so that the pivot table would automatically refreshes itself when it has been changed. Please help me in creating the pivot tables that automatically refreshes. Thanking in advanced.

[Badrunath]

I was also thinking of this so I created the code and I think that you should run the code in the worksheet_Calculate Event and the code is as follows:

Private Sub Worksheet_Calculate () 
    Dim mh As PivotTable 
     
    Application.EnableEvents = False 
    For Each mh In Me.PivotTables: mh.RefreshTable: Next pt 
        Application.EnableEvents = True 
End Sub

This code will help you to solve the problem.

[MahaGuru]

I think that the code for the raw data sheet should be as per you only have to mh it as the opposed to the raw data. The code is as follows:

Private Sub Worksheet_Calculate () 
Dim mh As PivotTable      
Application.EnableEvents = False 
For Each mh In Sheets ("PivotTable").PivotTables: mh.PivotCache.Refresh: Next pt 
Application.EnableEvents = True 
End Sub

So this code should reside above the raw data sheet and the object should be in the VBE and it should not be in the Pivot table.

[GeforceUser]

I would like to write the full code in the expanding order such as the one which is using the Change event. An in this case it will refresh and the pivot tables on the worksheet. In this case the macro would be residing on each of the desire the coding is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim mh PivotTable
    Dim hmk  As Worksheet
    Application.EnableEvents = False
    For Each hmk In Application.Worksheets
        For Each mh In hmk.PivotTables
            mh.PivotCache.Refresh
           ' MsgBox ("Refreshed pivot table '" & mh.Name & "' in sheet '" & hmk.Name & "'.")
           ‘Use above line only for testing/confirmation.
        Next mh
    Next hmk
    Application.EnableEvents = True
End Sub

[Bull50]

From the discussion going on I think that you have to open the pivot table and after that you have to right click on the “pivot table option” which is in the popup menu and after the window is open in the pivot table then you have to just select the data tab and after that you have to check the checkbox called "Refresh data when opening file". And after that click on the OK button. And this will automatically refreshes the pivot table this is the simple and also an easy method.

[DotNetUser]

There are several another “events” that are available in the spreadsheet and this you can be able to place this in the VBA code and after that you can be able to refresh the pivot table and after that when the "Worksheet_Calculate" event fires. You have to just use the macro code it is as follows:

Private Sub Worksheet_Calculate ()
    'If data on this worksheet changes, refresh the pivot table
    Sheets ("Pivot").PivotTables ("PivotTable1").Refresh Table
End Sub

[$tatic]

You can try this you have to just right click on the worksheet that is having the source code and the pivot table and then you have to select the view code option. This will help you with the view code and this will associate a particular sheet and the type of sheet is like this

Private Sub Worksheet_Change (ByVal Target As Range)
Me.PivotTables(1).Refresh Table
End Sub

I hope that this will solve the problem.