Create Pivot Charts with VBA


Well-known Member
Aug 12, 2009
Hi everyone!

I'm in trouble trying to create Pivot Charts with VBA: I've recorded a macro and tried to edit it to make it prettier / more functionable but with no such luck.

Here's what I'm trying to do:

I've got my data area (=25 columns / several thousands rows) on my worksheet and I'm trying to create another workbook with just the pivot tables / -charts and not just one of them but seven of them. The basic setup of all the charts & tables is the same: They all have the same page fields, row fields and column fields and the only difference is the data field.

Is there an easy way to make this happen in VBA?

Also, most of the charts will be having the same kind of formatting on them (get rid of the grey background and make them line charts with company colors and that's it). Is there a way to use the same macro for all charts using the same formatting or do I have to record each separately if the names are different? I'm also going to have to do the same kind of new workbooks over and over again (= once a month for several customers) and I wanted to ask if there's a way to have the macro create the new workbooks with chart formatting macros & events already in the modules or would it be better if I used one premade workbook as a basis and just update the pivot cache (and how would I do that if the data area is in a different workbook?)

I'd prefer to have all the creating macros in just the "main" workbook and as few macros and all in the other ones because of the size: I'm not even half way done yet but the size of the basic workbook is already 20 Mb (and growing).

Thank you already in advance.

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Watch MrExcel Video

Forum statistics

Latest member