Link to home
Create AccountLog in
Avatar of CarenC
CarenC

asked on

Excel 2003 PivotTable.ClearTable

My spreadsheet has a pivot table on 5 different worksheets.  Two of the pivot tables use the same datasource. The others each have their own datasource.

For the 2 tables that share the same datasource I get a warning message when I run pvtTable.ClearTable.  The message is:
The pivottable report is based on the same data as at least one other pivottable report. Clearing the pivottable report will remove the following from all the pivottable reports: grouping, calculated items, calculated fields, custom items.
See attached .jpg


Dim arrColumns
    Dim nColumns As Integer
    Dim wsReport, wsData As Worksheet
    Dim pvtTable As PivotTable
    
    Application.ScreenUpdating = False
    
    Set wsReport = Worksheets(sReportType)
    Set wsData = Worksheets(sReportType & "Query")
    theDataSource = sReportType & "Results"
   
    'Put the Column Headings of the Query Results into an array
    wsData.Select
    col = Range("A1").End(xlToRight).Select
    nColumns = Selection.Column
    ReDim arrColumns(nColumns - 1)
    For i = 0 To nColumns - 1
        arrColumns(i) = Cells(1, i + 1).Value
    Next
    
    'Clear the old pivot table and place the pivot table fields
    wsReport.Select
    Set pvtTable = wsReport.Range("A10").PivotTable
    
    pvtTable.ClearTable                 'this is the line that causes the message

Open in new window

pivottable-warning.jpg
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of CarenC
CarenC

ASKER

Thanks.  Didn't know that code.