CODEWITHSUNDEEP
excelvbscriptexcel-2007vba
arun_roy
arun_roy

Reputation: 601

Pivot table creations

I have a doubt that can one sheet have multiple Pivot tables? If so is there any way to define their names in the sheet?

I wrote the below code to update/Refresh the Pivot tables defined on different sheets,but gettings following error:

 Dim objExcel1,strPathExcel1
 Dim objSheet1,objSheet2,objSheet3

 Set objExcel1 = CreateObject("Excel.Application")
 strPathExcel1 = "D:\AravoVB\Copy of Original    Scripts\CopyofGEWingtoWing_latest_dump_21112012.xls"

objExcel1.Workbooks.open(strPathExcel1)
Set objSheet1 = objExcel1.ActiveWorkbook.Worksheets("Business Process Matrix")
Set objSheet2 = objExcel1.ActiveWorkbook.Worksheets("Workflow Status Matrix")
Set objSheet3 = objExcel1.ActiveWorkbook.Worksheets("Business Hierarchy Martix")

objSheet1.PivotTables(1).PivotCache.Refresh
objSheet2.PivotTables(1).PivotCache.Refresh
objSheet3.PivotTables(1).PivotCache.Refresh

ERROR : Subscript out of range

Can you help me here?

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?

Thanks

Upvotes: 1

Views: 1061

Answers (1)

MikeD
MikeD

Reputation: 8941

You can have multiple Pivot tables in one single sheet. Create them, then right-click them, select "Pivot Table Options ..." and view/change their names. Normally you don't need to do this, as Excel automatically names the tables [PivotTable*N*] with N being 1, 2, 3, ...

However you should avoid having multiple Pivots in a single sheet if you often change their look (e.g. add or remove columns and/or rows)

EDIT: in response to your comment ... yes you can ... in Excel2010.VBA the relevant objects/methods are

ActiveWorkbook.PivotCaches.Create(SourceType:=..., SourceData:= ..., Version:=...).CreatePivotTable TableDestination:=..., TableName:=..., DefaultVersion:=...

The Source data is defined through the PivotCache object, destination defined through the concatenated CreatePivotTable method.

Later on you use

ActiveSheet.PivotTables(...).PivotFields(...).Orientation and .position

and 

ActiveSheet.PivotTables(...).AddDataField ...PivotFields(...), "header", xlSum

to specify your Pivot table design.

Best is to start recording a macro, create a Pivot table, stop the recorder and examine the generated code.

Upvotes: 3

Related Questions