vba excel create pivot table by cache or new

Question

on a vba excel macro tool, I need to create many new sheet and pivot tables from two data sheet.

we have two way

use pivot cache to create new pivot table.
create new pivot table with same data sheet again and again.

What will be the impact if I choose second approach,

will this make our excel file heavier?

want to skip the first approach because if require dependency that first pivot table should be there in every excel workbook?

using excel 2010.

I think there is third attempt which is a mix of yours... You need to 'have' PivotCache somewhere anyway. What I do in similar situation is that I create ONE pivot Cache referring to Range, and than I create a lot of PivotTables using that PivotChache and it works really fine... Is this any of your ways? — Kazimierz Jawor
this macro need to run on many different files, two data sheet must be there for sure and data and data range will be different. using pivot cache is easier, but for this we have to make sure it must be there in excel file. — Lalit
programetically if I create once, will I able to reuse it. how can I identify it. does it have name. — Lalit
So, if you have one pivot table for sure, you right, I would use that PT cache as PT keep link to data on its own. It seems to be good and reliable option. However, I have no experiences about efficiency of that way. — Kazimierz Jawor

Vishwas - xCELL Solutions Vishwas - xCELL Solutions · Accepted Answer · 2013-04-06T08:25:56

I did one project where it will create pivot table in existing sheet and copy the data to the new spread sheet. If that is your question..please refer to the below code

Dim objtable as pivottable, objfield as pivotfield
set objtable = sheet1.pivotwizard
   ptname = activesheet.pivottables(1).name 'this code is to give pivot table name which will help in creating new pivot tables
   set objfield.objtable.pivotfields("Month") 'this code will select month in page field
 objfield.orientation = xlpagefield
set objfield = objtable.pivotfields ("Dept & Mgr") 'this code will select dept & mgr in page field
objfield.orientation = xlpagefield
set objfield = objtable.pivotfields("Legal Entity") 'this code will select legal entity in row field
objfield.orientation = xlrowfield
set objfield = objtable.pivotfields("Cost Code") 'this code will select cost code in row field
objfield.orientation = xlrowfield
set objfield = objtable.pivotfields("CC Description") 'this code will select CC Description in row field
objfield.orientation = xlrowfield
set objfield = objtable.pivotfields("User") 'this code will select Usr in row field
objfield.orientation = xlrowfield
set objfield = objtable.pivotfields("Carrier") 'this code will select carrier in row field
objfield.orientation = xlrowfield
set objfield = objtable.pivotfields("Description") 'this code will select Description in row field
objfield.orientation = xlrowfield
set objfield = objtable.pivotfields("Amount Incl Tax") 'this code will amount incl tax in Datafield
objfield.orientation = xlDatafield
activesheet.pivottables(ptname).format xlreport6 '

vba excel create pivot table by cache or new

1 Answers