VB Macro for multiple Pivot table in different tabs

Dear Experts ,

Can anyone explain me to write macro with looping to create multiple Pivot table in different tabs from one pivot source. I have attached excel files and insert two pivot tables as I required.
I have variable from X1 to X33 in my rows and I need pivot table Name in Row label, Time in Colum label and X1 in Values Field (value filed must be summarized as Sum)
I need 33 Pivot tables in different tabs while keeping constant Row label and Colum label as name and time , and changing value field (value filed must be summarized as Sum) from X1 to X33.

I have inserted two pivot table for get it clear my requirement.

Thanks and Regards.

NNNN

If this is a one time requirement, then there is a relatively easy way to do it without VB. I see that you have the same pivot table structure, but include a different x1, x2, ... as the value in the pivot table.

Select the two sheets on which you already have the pivot table and right click the mouse. Once of the options on the menu is Move or Copy. Select Copy and click on a position after which you would like to create the new sheets. Check the box that says Create a Copy and click OK. You now have two duplicate sheets. Select the four sheets (two original and two copies) and repeat the process. You now have 8 copies and so on.

Then go back rename the sheets and click on the appropriate value you want to display in each sheet.

I can suggest an different approach: create one sheet, and give the user a drop down box to select which variable to summarize. This will take some VB coding. Let me know if you are interested in this approach: I can give it a try.

Dear Dflak ,

Thank you for your replay . This is part of daily repitive report . So VB approch is more suitable . Pls go head with your second option .

Thanks .
NNNN

Here is a copy of your sample file. I truncated some of the data for the purposes of testing.

The first thing I did was highlight the range of headers (C1:L1) and gave it a name: FieldList. I use this as a source of a data validation in cell B1. This assures that you must select a good name. You will have to select the column headers on your final spreadsheet and give it a name.

I made a change event on the page containing the pivot table that fires when this dropdown list is changed.

Please Login or Register  to view this content.

This event calls the subroutine that changes the pivot table. It turns off all values and turns back on just the value you want.

Please Login or Register  to view this content.