Help with converting 2007 pivot to work on 2003

Hi,

Does this help?

http://www.brighthub.com/computing/w...les/24484.aspx

Point being you need to create the Pivots in Compatibility Mode ... if they were created in XL2007 you can not convert them retrospectively.

Originally Posted by mztexas

I would like for the pivot tables to update with the added data when refreshed but not sure how to do that either in 2003.

Use a Dynamic Named Range as the source for the Pivot (see link in sig. for more info) and/or use an appropriate VBA event to refresh the Pivot cache as when that data is altered.

Creating the Dynamic Named Range itself is simple enough, eg:

Name: _PTData
RefersTo: ='Applicant Data'!$A$2:INDEX('Applicant Data'!$I:$I,MATCH(REPT("Z",255),'Applicant Data'!$A:$A))

When creating the PT you would enter _PTData as source
(if you continue to use existing 2007 versions you can go to Pivot Table Options bar on Ribbon [when PT active] and click Change Data Source)

In theory you can use a Change event on the Application Data object to refresh the PT Cache(s) as and when that data is altered:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim PT As PivotTable, ws As Worksheet
    On Error Resume Next
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        For Each ws In ThisWorkbook.Worksheets
            For Each PT In ws.PivotTables
                PT.PivotCache.Refresh
            Next PT
        Next ws
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

but you may need to be careful regards positioning of the Pivots

In reality - I would suggest creating a back up and running some tests to see if you can get what you want.