Pivot Tables - Defaulting to 'Classic View' in Excel

JeffWolf

New Member
Joined
Feb 20, 2006
Messages
2
I've recently been 'forced' to use Excel in Office 2010. For various reasons I don't like the look and functionality of the new pivot tables - I prefer the 2007 version. Yes, I know, I know, call me a Luddite.

I have worked out that I can modify the way pivot tables look and feel by right clicking on the body of a new pivot table and setting the 'Classic' Pivot Table Layout and clicking a few buttons in the Display tab.

The problem is I have to do this every time I set up a new pivot table - several times a day - and it gets a bit tedious.

I thought I could get round it by simply recording a macro and assigning it to a button on my Quick Access Toolbar so that it performs the same actions each time.

This doesn't work because the VBA commands stored behing the Macro seem to record the pivot table number and therefore the macro falls over when used on a new pivot table with a different number.

I'm wondering if it is possible to modify the VBA to make it generic rather than specific to a particular pivot table? I have no VBA skills at all so any solution needs to be really Mickey Mouse.

Or maybe there's another way to achieve the same end result without using a macro?

Cheers
 
Last edited:
This solution is not the best but you can try to use it. It will loop through all Pivot's in active sheet and change them to the Classic View

Code:
Dim PvtTbl As PivotTable

For Each PvtTbl In ActiveSheet.PivotTables
    With PvtTbl
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With
Next PvtTbl
 
Upvote 0
Hi,

Maybe try the following code:

Code:
Sub example()


Dim ws As Worksheet
Dim pt As PivotTable


For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
        pt.InGridDropZones = True
        pt.RowAxisLayout xlTabularRow
        pt.TableStyle2 = ""
    Next pt
Next ws


End Sub

This changes all the pivot tables in your workbook to the classic style.
Here is an article with more detailed code if you want further adjustment/formatting of the pivot table: http://www.contextures.com/excel-vba-pivot-format-macro.html

(Although I like the new style...I think you might like it once you get used to it!)
Also, why are you creating multiple pivot tables every day?
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top