Excel 2010 - double-clicking on pivot table, multiple worksheets

[JungJung]

I upgraded to Excel 2010 from Excel 2000 a couple months ago. In Excel 2010, after double-clicking on values in a pivot table to create a new worksheets, I'm unable to highlight those worksheets and add a column to all of them. I was able to do this in Excel 2000... I can't figure out what's stopping it from happening for the life of me. Am I missing something obvious here? Has anyone else come across this?

Thanks for the help!

Adam

 

[JungJung]

I was finally able to word my question well enough for a Google search to understand what I was asking... The new sheets created when double-clicking on a pivot table are created as tables rather than as ranges. To convert a single table to a range, you can right click, choose Table, then choose Convert to Range.

Here's some code that loops through all worksheets in a workbook and converts all tables to ranges:

Dim wks as worksheet, objList as listobject
for each wks in activeworkbook.worksheets
for each objlist in wks.listobjects
objlist.unlist
next objlist
next wks


Hopefully this helps someone else who runs into the same problem!

 

[Neil1981]

I was finally able to word my question well enough for a Google search to understand what I was asking... The new sheets created when double-clicking on a pivot table are created as tables rather than as ranges. To convert a single table to a range, you can right click, choose Table, then choose Convert to Range.

Here's some code that loops through all worksheets in a workbook and converts all tables to ranges:

Dim wks as worksheet, objList as listobject
for each wks in activeworkbook.worksheets
for each objlist in wks.listobjects
objlist.unlist
next objlist
next wks


Hopefully this helps someone else who runs into the same problem!

Hi,
Here is my problem:

From a pivot table, I created multiple worksheets (as you had done above) by double clicking on the amounts one by one.

Now, when I select all those work-sheets together (Group), I find the "CONVERT TO RANGE" option grayed out, so, when all work-sheets are grouped, I cant add columns and rows to the multiple worksheets at the same time.
Is there a work-around for this please?
Please note: I don't know anything about VBA.
Kindly mail replies to my gmail as well: richards.nicholas@gmail.com

Thanks in advance.

 

[JungJung]

Hi Neil1981,

You can't use the Convert to Range feature on multiple worksheets, so you'll need to use VBA to get this done.

Paste the code below in the Visual Basic Editor and then run the macro - that should do the trick.


Sub ConvertToRange()

Dim wks As Worksheet, objList As ListObject
For Each wks In ActiveWorkbook.Worksheets
For Each objList In wks.ListObjects
objList.Unlist
Next objList
Next wks

End Sub