Pivot table Programming in VBA

Question

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.

rajah9 · Accepted Answer · 2012-12-06 15:46:48Z

1

If you insert a pivot table on your data, drag these columns into the Row Labels:

Name
Fruit.

Then drag this column into the Column Labels:

Condition of Fruit.

Drag the same column into Values:

Condition of Fruit.

The pivot table looks almost the same as yours (except the fruit comes under the name, instead of repeating the name for each fruit).

Screen shot pivot table

Whenever you update the data in sheet 1, you may have the pivot table reflect this by right-clicking on the table and choosing Refresh.

edited Dec 6, 2012 at 15:46

answered Dec 6, 2012 at 14:31

rajah9

12.5k5 gold badges48 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Arup Rakshit Over a year ago

may i know,how to do pivot table refresh automatically using vbscript?

rajah9 Over a year ago

Sure, do a search for "VBA automatic refresh pivot table"; the top several hits had the right answer, for example, techonthenet.com/excel/macros/refresh_pt.php.

Arup Rakshit Over a year ago

Here in this post i have seen some excel sheet pivot table formation screenshot provided by you. Did you delete that?

rajah9 Over a year ago

No, I didn't delete the screen shot. Please try to reload this page. If that fails, you may find the screenshot at i.sstatic.net/qjMnB.png.

Arup Rakshit Over a year ago

Hi rajah, i searched but couldn't find out any pivot table automation idea,what i was looking for.Could you give me an idea for the same?

|

Collectives™ on Stack Overflow

Pivot table Programming in VBA

1 Answer 1

8 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Linked

Related