Solved

Excel 2003 PivotTable.ClearTable

Posted on 2010-11-09
2
725 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:CarenC
2 Comments
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 34096098
Try turning Application.DisplayAlerts off (by setting it to False) prior to clearing your table.

Brad
Sub PTdeleter()
Dim pt As PivotTable
Application.DisplayAlerts = False
For Each pt In ActiveSheet.PivotTables
    pt.ClearTable
Next
Application.DisplayAlerts = True
End Sub

Open in new window

0
 

Author Closing Comment

by:CarenC
ID: 34096588
Thanks.  Didn't know that code.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now