Excel Pivot table creation through vbscript

Question

I have a requirement where I set up three pivot tables created from the same sheet #3. Now the sheet #3 is also getting populated with the data from sheet#1 and #2. every new population of sheet first clearing the data and then populating it with new fresh data. and for that my pivot table refresh code is not working and giving the error that table columns are not found even if the data and column is there.I am quite sure that this is happening due to the data clear. So I am wondering to have some way to create such pivot tables at run time through the script. Is it possible?

UPDATE

I have a data Excel sheet in the below format:

Name   Fruits    Condition of Fruits
=====  ======    ====================

Ram    Apple       Good

Jadu   Apple       Bad

Tina   Orange      Good

Ram    Orange      Good

Tina   Apple       Bad

Ram    Apple       Bad

Output of the Pivot program in the other sheet of the same excel, say in sheet(2) should be like this:

Name  Fruits   Condition of Fruits
====  ======   ===================
               Good       Bad    Total
               ====      =====  ======
Ram   Apple     1           1     2
Ram   Orange    1           0     1
Tina  Apple     0           1     1
Tina  Orange    1           0     1
jadu  Apple     0           1     1
===================================
Grand Total     3           3     6
===================================

The above format needs to be present in the Excel in the sheet(2), whenever sheet(1) will be having source data within it as shown in the first table.

I want Macro program to create using Excel pivot table concept.

Can you suggest any code settings, for the above Pivot creation?

@Tukai either you do pivot or a code. Manage some consistency. Why mix both? — bonCodigo
@bonCodigo I have set it manually but now that manual set up leading me an error,so i am thinking to set it up also dynamically? so any chance to do the same? — CodeLover

Kanwaljeet Mehta Kanwaljeet Mehta · Accepted Answer · 2013-01-01T09:44:50

Instead of directly going for creating pivot table from VBA try this.

If you are using vba function to clear all data and then feel it with new data stop erasing for data. Instead of erasing data just replace the old data with new data cell by cell.

Let me know if this helps.

If you can provide the excel file I can have look at the problem.

Excel Pivot table creation through vbscript

1 Answers