• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Create Multiple Pivot tables Charts to different Workbook in same excel file

S

santhurcv

Member
Hi,


I have a tickets Report for say some 20 customer of 10 months and one single Heading for all, and i want to create similar Pivot tables and chart for all the Customer in different workbook (TAB)in same excel file.


Can some one please help me on this ?


Thanks in advance.
 
Hi santhurcv,


Why don't you upload your sample file here to have a better understanding of your data.


http://chandoo.org/forums/topic/posting-a-sample-workbook


Please let us know where do you want to create the pvt table and charts....in different workbooks or different tabs/sheets of a same workbook?


Also let us know what kind of charts you want, what the charts should represent etc.


Regards,

Kaushik
 
Hi Kaushik,


Sorry i can not upload those file which carries very sensitive data. here is my Requirements


1. I create same pivot tables and charts for all the 20 customers.


2. I Need this same pivot tables and charts in different tabs/sheets of a same workbook but 1 to 20 customer 1 in sheet1, 2 in sheet2, 3 Sheet3.. etc until 20 customers.


3. i use Pie, Bar charts basically,,,,


Thanks
 
santhurcv


You mention tabs!, this is happening quite often now in many postings, are you referring to the top "named" row in a table or the sheet tabs, you mention tabs/sheets. is it one or the other.

You could remove sensitive data and replace with junk data and upload to Dropbox so that we could get a better understanding or what you are after
 
hi i have create a file with sample data and i have created a Pivot tables and charts for ABB customer, the same pivot tables and Charts need to be created for CNN. BCC... etc.


Note : i have only created 2 Pivots and chart but actual pivot and Chart in original data(cannot be shared) are more then 12 pivot and charts to it


https://dl.dropbox.com/u/94380636/New%20Microsoft%20Excel%20Worksheet.xlsx
 
santhurcv,


Do you mean to say the same pivot table and or chart for 20 customers in 20 different sheets? I mean one pvt/chart for one customer should be in one particular sheet...so on and so forth...is that all you want?


And yes,as requested, please upload a sample file (removing the sensitive data) with dummy data with one chart an pvt table for example...


Regards,

Kaushik
 
Ok...


I could not check as you uploaded the file when I was posting.


Thank you for uploading the file.


Will get back to you soon


Kaushik
 
santhurcv,


Just quickly had a look at your data, pvt table and chart


Your requirement can be achieved by the help of macro. It seems that you have 6 unique customers. So the logic would be, macro will loop through the unique customers list.And for each customer name, the report filter value will be changed accordingly.Then it will be just copy and paste job(copying the entire sheet and paste it to next available sheet.


Would that be acceptable to you if I use macro to solve this?..Because I can not think of a formula based approach to solve this!!


Regards,

Kaushik
 
santhurcv


Please have a look at the upload to see if this is what you are talking about, because I do not understand why you need a separate pivot for each customer as the filters in a pivot let you show what you want.


https://dl.dropbox.com/u/75495784/santhurcv.xlsx
 
Hi Santhurcv,


Sorry for delay in reply from my end.


However, I could see that bobhc has already replied with a solution.


I have also tried to provide you a solution but bit in a different approach.


Get the file from here:

https://hotfile.com/dl/164861261/9818fc8/Get_Pvt_and_Cht_in_different_sheets.xlsm.html


The macro will create the same pivot and chart for each customer in a different sheet and name the sheet with that customer.


The example pivot and chart I have kept in a sheet called "ExampleChtPvt"


Macro is creating the sheets for each customer from this reference sheet only.

Hit the button placed in this sheet(ExampleChtPvt) to get your report.


Regards,

Kaushik
 
Good day kaushik03


Although the solution I posted does a job in copying the work sheets you still have to rename the copied sheets where as your solution is far tidyer as this is done by the VBA so there fore yours is better :)
 
Nothing like that bobhc( better and all...)!!!


It is just I thought to share the solution the way I envisioned this...


You have done a wonderful job dear and moreover, you have taken the initiative to post the solution early than me.... :)


Hope you are having an awesome weekend...


Best regards,

Kaushik
 
You must log in or register to reply here.
Back
Top