Not working: ClearTable

[jomili]

I've recently started using the "ClearTable" command, because our IT group keeps changing the layout of the Pivot Tables that my macros harvest. Everything's been working okay except for one workbook; the pivot table there won't clear, it gives me a RunTime 1004 error. The code I'm using is pretty basic:

Please Login or Register  to view this content.

The PT I'm trying to clear is based on a query to a remote database I don't have access to, but other PTs based on other remote data sources I can clear easily. This one is the most detailed, with massive amounts of data behind it. Could that be the source of the error?

I can't provide a sample workbook as it's proprietary information and a HUGE file.

Any help is greatly appreciated.

[AB33]

Please Login or Register  to view this content.

More likely

Please Login or Register  to view this content.

[jomili]

AB33, Your codes worked, but not the way I wanted. Both of your remedies completely deleted the pivot table. What I want is to destroy the constructed pivot, and go back to the beginning of the pivot, so I can recreate it to suit me.

However, you did cause me to reconsider how I had my argument phrased. I tried it again this way:

Please Login or Register  to view this content.

, and it *SEMI* worked: I didn't get the RunTime error, but instead got a box saying "Excel cannot complete this task with available resources......".

[AB33]

I only know two methods- Clear and delete.
It is the sheet name then(dot) the name of the pivot table (dot) and the range TableRange2

[jomili]

AB33, the "ClearTable" dialogue resets the PivotTable to the state it had right after it was created, before any fields were added to it. You can see the writeup on it at

. As stated, I've been using it, with no issues, until I came to THIS pivot table.

[AB33]

Okay,
I have now tested both lines and both have the same outcome

Please Login or Register  to view this content.

We are now back to square one. What do you want the code to do to a PT? In other words, what is not working?

[jomili]

Well, first of all, these two codes DON'T do the same thing, at least when I run them (Excel 2010):
This code resets the PivotTable to the state it had right after it was created, before any fields were added to it. In my version of Excel, the result of the code (not counting THIS SPECIFIC pivot table) is the basic Pivot Table structure you see after you do "Insert Pivot Table".

Please Login or Register  to view this content.

This code deletes the pivot table. After running it, I have a blank sheet:

Please Login or Register  to view this content.

I want to do what the first code does, which it does very well, EXCEPT for this one pivot table.

[jomili]

Bumping up, as I'm still in need of a solution.

[jomili]

I've found a possible solution by looking backwards instead of forward. I can't get the "ClearTable" function to work on this pivot, no matter what I try. But my old 2003 code seems to do the trick. See the two macros below; for 99% of my pivot tables, both work identically, but for this particular Pivot, only the "ClearPivot_2003" works, the other bombs.

Please Login or Register  to view this content.

[AB33]

If one works in 2007, but in 2003, I imagine it could be because PT had changed a lot in 2007. There are many features of 2PT-2007 which were not in 2003.

[jomili]

Agreed. But the problem is, the "ClearTable" came about in 2007, and is for 2007 and beyond Pivot Tables. The Pivot in question IS a 2010 pivot table, but "ClearTable" doesn't work, instead a solution for 2003 and earlier pivot tables works. So something is funky!

[jomili]

Still looking for a reason that "ClearTable" doesn't work, or a way to get it to work, as I'm sure this issue will come up again.

[AB33]

I do not know. I have never used that method. This always work for me.
TableRange2.Clear

[jomili]

How does that work for you? What does the TableRange2 do for you when you invoke it?

When I run it, no pivot table is left. To get a pivot table back, I have to go to "insert Pivottable". Since for this one the source is remote and locked up, I can't connect back to it, so I can't recreate the pivot. It's just plain gone, when what I want is to go the barebones begining, and modify it to the way I want it to be.

With the "ClearTable" option, my pivot still exists, it's just that all the fields have been pulled out, but they still exist, so I can pull any or all of them to recreate the pivot as I wish.

[AB33]

Do you mean the fields are hidden?

Please Login or Register  to view this content.

[jomili]

Yes, the Pivot Table fields are hidden. The last code you posted hides the Field List, but still leaves the fields in the pivot table.

These two lines code do essentially the same thing:

Please Login or Register  to view this content.

[AB33]

I am now lost

[jomili]

They say a picture is worth 1000 words, so let's see. Take a look at the attached and see if makes it clearer.

[JosephP]

how about if you call clearallfilters before cleartable?

[jomili]

I run into the same error. I'm wondering if the pivot table structure might be the problem. I've been calling this a 2010 pivot, becuase I'm using 2010 and it's a .xlsx workbook, but the pivot is an old-school pivot, so maybe the ClearTable isn't supposed to work on it. But, I have other "old school" pivots that DO respond correctly.