Excel97 - Pivot tables 1

Have you thought about refreshing the pivot table in a Worksheet_SelectionChange event on the worksheet that contains the pivot table

No, but that is a god idea. I guess I can figure out how to make the macro for that, but if you have it ready.....

Edvard

Put the code below in the MS Excel Object - ThisWorkbook
I've written it so it only refreshes the pivot table if you have altered a cell that is part of the source data to it.
Make sure to change pvtSource pivot table and worksheet that's assigned to it so it matches yours (at the mo, it refers to the first pivot table and worksheet).

'code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Dim strSourceData As String
    Dim strWorksheet As String
    Dim intLength As Integer
    Dim pvtSource As PivotTable
   
    'Get address of sourcedata
    Set pvtSource = Worksheets(1).PivotTables(1)
    strSourceData = pvtSource.SourceData
    intLength = InStr(1, strSourceData, "!")
    strWorksheet = Left(strSourceData, intLength - 1)
    strSourceData = Mid(strSourceData, intLength + 1)
    strSourceData = Application.ConvertFormula(strSourceData, xlR1C1, xlA1)

    If strWorksheet = Sh.Name Then   'check to see if target range is in the same worksheet as source data
        If Union(Range(strSourceData), Target).Areas.Count = 1 Then     'if part of source data
            pvtSource.RefreshTable
        End If
    End If

End Sub

Thanks, out of this I´v got a better ide. You see I have the pivot table in another sheet than the input data area, so what I can do is to make a macro that update pivot tabel on entering that sheet.

What should be the name of that macro be?
Private Sub Workbook_SheetEnter(ByVal Sh As Object, ByVal Target As Excel.Range) ??


Edvard

Put this code in the specific sheet with the pivot table under Microsoft Excel Objects found in the VBA window.

Private Sub Worksheet_Activate()
   PivotTables(1).RefreshTable
End Sub


Like Workbook_SheetChange, Worksheet_Activate is an Excel event that runs code when you have performed a specific action (in this instance selecting the worksheet). If you're not familiar with this, in the top right corner of the code window is a combobox with all events listed. Make sure the combobox to the left of that one has Workbook/Worksheet selected (depending on whether it is ThisWorkbook or a sheet).

Thank you, pleas answer so I can grade it.

Edvard