Thread: design Pivot Chart Formatting

Hi,

Your best bet would be to record a macro of you applying your formatting and then incorparate the pivot refresh command into it so that you refresh your pivots using a button (assigning your macro to a autoshape).

I'll get back to you with some sample code.

Heres some code to reresh all pivots in a worksheet, just paste in your recorded macro for applying your format.

I dont how much you know vba so here goes

Alt F11 open the editor, down the left hand side drop down menus look for module (if you have not recorded a macro yet - insert >module). There you will be able to put the code. Yor best bet is just to begin with recording a macro of you applying your formatting. Tools > macro etc. It wll record all keystrokes/mouse clicks so be carefull.

sub name()/ end sub

- are the beginning/end of your macro so dont delete them.

just paste the following info in to the macros. depending on what you want.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim pt As PivotTable
For Each pt In Worksheets(" The name of your sheet").PivotTables
pt.RefreshTable
Next pt
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

or you could reresh your tables then apply formatting macro

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh 
' repeat for all your tables, to find out table numbers record a macro of selecting each one in turn

Hope it helps (apologies if it does not make sense i'm a little rushed today)

Yeah, sorry should have picked up on that.

try inserting this above the line

 Sheets("Your SheetName").Select
ActiveSheet.ChartObjects("Your Chart Number").Activate

hope it helps

Hi...

Sorry to be so thick, but... ActiveSheet.ChartObjects("Your Chart Number").Activate

Where do I get the 'chart number' from? What is it referring to exactly?

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Follow up: I decided it was 'Chart1' from VBA explorer, but when entering this in the "Chart Number" part of the code and then running it, it gives Run Time Error 1004 - Unable to get the chartobjects property of the chart class on this line of code?

Hi,

No worries, for future reference.

All charts are automatically given a number during creation to help excel define them in vba etc. So you make a chart on a blank page excel calls it chart 1, you make another excel calls it chart 2 and so on. basically it helps excel reference them. Unfortunalty i dont know if this is limited to my companies version but excel does not allways go in a logical order so you can get chart 1 chart 2 chart 2345 etc. The best way to determine your chart numbers is to record a macro of you selecting them. So start recording your macro click on a blank cell and then click on your charts in an order you can remember, stop recording the macro press alt f11 to open the vba editor and find your macro in the modules and it will have all the chart numbers listed as you selected them.

now lets have a look at the error

can you zip and attach the file so i can take a look? dont forget to remove any confidential info ! like figures and replace them with examples.

Hi,

edit your code so it looks like this

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim pt As PivotTable
    For Each pt In Worksheets("Table").PivotTables
        pt.RefreshTable
    Next pt
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Sheets("Chart").Select
    ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:= _
                                "PWB Columns"

Hope it helps