Pivot Tables - Defaulting to 'Classic View' in Excel

[JeffWolf]

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

 

[skorpionkz]

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

Dim PvtTbl As PivotTable

For Each PvtTbl In ActiveSheet.PivotTables
    With PvtTbl
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With
Next PvtTbl

 

[circledchicken]

Hi,

Maybe try the following 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?