Macro for creating two pivot tables

Hello,

I am writing a macro that will create two pivot tables from the same data range. I am still learning the concepts of variables and loops, and this macro is trying to do both!

I am working from an example in john walkenbach's power programming with vba (2007). His example is far more complex than my situation...so i may be using too many variables or unnecessary loops.

I want the pivot tables to be created on the same sheet("Summary") on columns 1 and 6. Right now the error is occuring after the "create pivot table" comment in the code.

Many thanks for your help!!

Please Login or Register  to view this content.

Hi,

I find that using the record Macro function is really useful. Start the recording, do what you need to do manually, stop the recording and then use the code that was stored in the marcro as a 'starter for 10' usually works for me with a bit of tweaking.

ta

PKDJ

Unfortunately, the macro recorder does not use loops or variables...as far as i know. I have accomplished the task of using the same pivot cache to produce two pivot tables, although not simultaneously. Currently, my macro creates a pivot table, copys/pastes to a new sheet, then clears the pivot table fields and creates a new pivot table without reselecting the data, and finally copys/pastes to the recently created sheet.

I want the macro to use loops and variables because soon i will want it to create three or more tables.